I want to fetch all discussions and add the count of all UserDiscussion rows where column 'bookmarked' is true for the discussion.
SELECT d.*, COUNT(*) as BookmarkCount
FROM Discussion d
LEFT JOIN UserDiscussion ud
ON ud.DiscussionID = d.DiscussionID
WHERE ud.Bookmarked = true;
The proper way to write this is:
SELECT d.*, COUNT(ud.DiscussionID) as BookmarkCount FROM Discussion d LEFT JOIN UserDiscussion ud ON ud.DiscussionID = d.DiscussionID AND ud.Bookmarked = true GROUP BY d.DiscussionId;
The changes are:
COUNT()on a field from the second table. This will count the number of matches.
COUNT(*)will never return
0, in this case (the group would be filtered out).
WHEREcondition to the
ONclause. Otherwise, the
WHEREcondition would filter out
NULLvalues, turning the
LEFT JOINinto an inner join.
GROUP BYclause so you get one row per discussion id.
Note: Grouping by
DiscussionId is fine, assuming that it is the primary key on
Discussion. In general, it is better to include all unaggregated columns in the
SELECT in the
GROUP BY. But it is okay (and compliant with ANSI SQL), when the
GROUP BY is by a primary/unique key.