SQL with 0 counts

Question

I have the following sql query :

SELECT DATE(procedures.start) date, name, COUNT(procedure_types.id) count
FROM `procedure_types`
  LEFT OUTER JOIN procedures on procedure_types.id = procedures.procedure_type_id
WHERE (DATE(procedures.start) = '2009-10-24')
GROUP BY DATE(procedures.start), procedure_types.id
ORDER BY DATE(procedures.start), procedure_types.id

There are two tables procedures & procedure_types. Procedure has procedure_type_id which points to the procedure_types table.

The query is executing fine, but I want to list the procedure_types with count(0) even if they aren't being referenced by any procedures on that date.

I know it's something to do with the joins..can someone please help ?

Answer 1

Your WHERE clause is excluding procedure_types with no procedures. You need to move it to the JOIN:

SELECT DATE(procedures.start) date, name, COUNT(procedure_types.id) count
FROM `procedure_types`
LEFT OUTER JOIN procedures on procedure_types.id = procedures.procedure_type_id
    AND DATE(procedures.start) = '2009-10-24'
GROUP BY DATE(procedures.start), procedure_types.id
ORDER BY DATE(procedures.start), procedure_types.id

Answer 2

Left Outer Join will select all records from Procedures table and only matching from Procedure_Types. You want to select all records from Procedure_Types even if no matching record in Procedures, so you need Right Outer Join. See this for more info on JOINS.