COUNT(*) and LEFT JOIN

Question

This query:

SELECT staff.staff_id, COUNT(references_table.staff_id) 
FROM staff 
LEFT JOIN references_table USING (staff_id)

returns this:

staff_id    COUNT(references_table.staff_id)
1            2

How can I get it to return 0 as the count for staff_ids that don't have any references?

Answer 1

Try a left outer join.

Answer 2

a GROUP BY clause will do the trick

SELECT staff.staff_id, COUNT(references_table.staff_id) 
FROM staff 
LEFT JOIN references_table USING (staff_id)
GROUP BY staff.staff_id