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?

Answer 1

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.