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 don't 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 aren't any ratings. This query returns only the ones with ratings.

I tried left join, right join, full join, still same thing.

Using CodeIgniter Active Records:

$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?

This is the definition of a LEFT JOIN though I think your use of the AVG aggregate isn't going to work for this — Phil
– Phil, Commented May 3, 2011 at 2:11
I have a feeling you will need a GROUP BY p.id, p.name however I still don't think the third column is going to show the correct values. I may be wrong but too lazy to test this myself — Phil
– Phil, Commented May 3, 2011 at 2:14
GROUP BY did the trick.. if you could answer it as a whole answer, i can give you credit :) Thank you — tpae
– tpae, Commented May 3, 2011 at 2:18

Phil · Accepted Answer · 2011-05-03 02:21:52Z

3

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

GROUP BY p.id, p.name

To ensure all project references are present regardless of joined ratings, use a LEFT JOIN.

answered May 3, 2011 at 2:21

Phil

166k25 gold badges265 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stefan H · Accepted Answer · 2011-05-03 02:14:11Z

3

The sql that you want would be

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

I'm not certain how to do that with code igniter.

answered May 3, 2011 at 2:14

Stefan H

6,6934 gold badges26 silver badges35 bronze badges

Comments

Mike Neumegen · Accepted Answer · 2011-05-03 02:27:01Z

2

Try this:

$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.

edited May 3, 2011 at 2:27

answered May 3, 2011 at 2:21

Mike Neumegen

2,4861 gold badge24 silver badges39 bronze badges

Comments

morgar · Accepted Answer · 2011-05-03 02:14:01Z

0

Query 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 that in CodeIgniter :)

answered May 3, 2011 at 2:14

morgar

2,41718 silver badges17 bronze badges

Comments

Richard Schneider · Accepted Answer · 2011-05-03 02:22:24Z

0

You can get an outer join by do this:

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

answered May 3, 2011 at 2:22

Richard Schneider

35.5k9 gold badges59 silver badges74 bronze badges

Collectives™ on Stack Overflow

SQL Join question, return null if second table is empty

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related