Group By , COUNT(*) and JOIN

Question

When running the SQL below, any placemark_types.id that doesn't have a id match in placemarks table does not return anything in the results. I'm trying to at least get a type_count of 0 back where there are no matching records in placemarks.

So if I have a placemark_types.name of 'Warning' and there are no 'Warning's in placemarks table, id like to get the columns | 0 | 'Warning'|

SELECT COUNT( * ) AS type_count, placemark_types.name
FROM  `placemark_types` 
JOIN placemarks ON placemark_types.id = placemarks.type_id
WHERE placemark_types.parent_id =0
GROUP BY placemark_types.id

Answer 1

MySQL, by default, performs an INNER JOIN, where only rows with a record in both tables are returned.

You can do a LEFT JOIN or RIGHT JOIN to tell MySQL to return one side of the join even if the other side is empty. In this situation, you'd want a RIGHT JOIN.

Answer 2

It is best to always specify what type of join you are doing. By default it is using an inner join. An inner join will only show items that have a match in both tables. In your example, you want all the records returned from the placemark_types and zero-to-many of the records from the placemarks table that match.

To do this just use a left join as shown below. The left join always returns all the records from the left side (placemark_types) and only the matching records from the right side (placemarks). Next lets get rid of the COUNT(*) and use COUNT(placemarks.type_id), because we don't want to count all the rows, just the ones with matches in the placemarks table. Else you would get 1 even when their where zero matches because it would still be counting the placemark_types.

SELECT COUNT(placemarks.type_id) AS type_count, placemark_types.name
FROM  placemark_types
LEFT JOIN placemarks ON placemark_types.id = placemarks.type_id
WHERE placemark_types.parent_id = 0
GROUP BY placemark_types.id

To understand this better, run the query without the GROUP BY. You'll see that even when their are no entries in the placemarks table, you will get the fields from the placemark_types table and nulls for the placemarks fields. Since the COUNT(placemarks.type_id) will not count nulls, this will give you the correct number.