SQL Join question, return null if second table is empty

Question

I have 2 tables.

table 1 (projects): id, name, type

table 2 (project_ratings): project_id, rating

Some projects do not have ratings.

SELECT `p`.`id`, `p`.`name`, AVG(pr.rating) FROM (`projects` p) JOIN `project_ratings` pr ON `p`.`id` = `pr`.`project_id` WHERE `p`.`type` = 'group' OR `p`.`type` = 'user'; 

I want to return all projects and return NULL if there are no ratings. This query returns only those who have ratings.

I tried left join, right join, full join, one and the same thing.

Using Active CodeIgniter Entries:

 $this->db->select("p.id, p.name, AVG(pr.rating)"); $this->db->from('projects p'); $this->db->join('project_ratings pr', 'p.id = pr.project_id'); $this->db->where('p.type', 'group'); $this->db->or_where('p.type', 'user'); $res = $this->db->get(); 

What am I missing?

Answer 1

When using the aggregate function ( AVG() in this case) you need to specify a GROUP BY with non-aggregate fields, for example

 GROUP BY p.id, p.name 

To ensure that all project references are present regardless of the combined ratings, use the LEFT JOIN .

Answer 2

In sql you want will be

  SELECT * FROM projects LEFT OUTER JOIN project_ratings ON projects.id = project_ratings.project_id 

I'm not sure how to do this with a code igniter.

Answer 3

Try the following:

 $this->db->select("p.id, p.name, AVG(pr.rating) as average_rating"); $this->db->from('projects p'); $this->db->join('project_ratings pr', 'p.id = pr.project_id', 'left'); $this->db->where('p.type', 'group'); $this->db->group_by('p.id'); $this->db->or_where('p.type', 'user'); $res = $this->db->get(); 

If there is no rating, average_rating will be NULL.

Answer 4

The request should be:

 SELECT `p`.`id`, `p`.`name`, AVG(pr.rating) FROM (`projects` p) LEFT OUTER JOIN `project_ratings` pr ON `p`.`id` = `pr`.`project_id` WHERE `p`.`type` = 'group' OR `p`.`type` = 'user'; 

(I don't know how to do this in CodeIgniter :)

Answer 5

You can get the outer join by doing the following:

  $this->db->join('project_ratings pr', 'p.id = pr.project_id', 'outer');