CodeIgniter - MySQL join when there are no rows in secound table

codeigniter, mysql

Solution

Use this instruction

$this->db->select('games.id, games.title, games.slug, games.dev_id, games.dev, games.plat_id, games.plat');
$this->db->select_avg('rates.rate');
$this->db->from('games');
$this->db->join('rates', 'games.id = rates.game_id','left');
$this->db->group_by('rates.game_id');
$q = $this->db->get();

Left join will bring multiple results. using avg and group by will restrict to fetch only one row against each record.

Problem

I have this query in model: ``` public function Games() { $q = $this->db->select('games.id, games.title, games.slug, games.dev_id, games.dev, games.plat_id, games.plat'); $q = $this->db->from('games'); $q = $this->db->join('rates', 'games.id = rates.game_id'); $q = $this->db->select_avg('rates.rate'); $q = $this->db->get(); ``` ``` return $q->result(); } ``` My goal is listing everything from `games`, additionally getting average `rate` from `rates` when available. Now it only shows those rows which are in both tables. How can I solve this?

Original source