Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When running the SQL below, any 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 of 'Warning' and there are no 'Warning's in placemarks table, id like to get the columns | 0 | 'Warning'|

SELECT COUNT( * ) AS type_count,
FROM  `placemark_types` 
JOIN placemarks ON = placemarks.type_id
WHERE placemark_types.parent_id =0
share|improve this question
up vote 6 down vote accepted

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.

share|improve this answer
Thank you very much! – Rob Nov 30 '12 at 19:57

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,
FROM  placemark_types
LEFT JOIN placemarks ON = placemarks.type_id
WHERE placemark_types.parent_id = 0

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.