You want an outer join for this (and you need to use person as the "driving" table)
SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments" FROM person LEFT JOIN appointment ON person.person_id = appointment.person_id GROUP BY person.person_id;
The reason why this is working, is that the outer (left) join will return
NULL for those persons that do not have an appointment. The aggregate function
count() will not count
NULL values and thus you'll get a zero.
If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null
~ Answered on 2013-02-10 00:04:21