Re: Counting records in related table

[David Fells]

Use a LEFT JOIN.

SELECT * , (IF (traffic.field IS NULL, COUNT(*), 0)) AS traffic_count
FROM members LEFT JOIN traffic ON (members.id = traffic.member_id)
GROUP BY members.id
ORDER BY traffic_count

Where field is some arbitrary field in the traffic table. It will be NULL when there is no matching record in the traffic table for a given member.