crispychicken crispychicken - 5 months ago 9
MySQL Question

MySQL join with count and where

I want to fetch all discussions and add the count of all UserDiscussion rows where column 'bookmarked' is true for the discussion.

Table: Discussion
-----------------
DiscussionID
Name
.....


Table: UserDiscussion
---------------------
UserID
DiscussionID
Bookmarked (Boolean)


This is my query but it is not correct:

SELECT d.*, COUNT(*) as BookmarkCount
FROM Discussion d
LEFT JOIN UserDiscussion ud
ON ud.DiscussionID = d.DiscussionID
WHERE ud.Bookmarked = true;

Answer

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:

  • Using 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).
  • Moving the WHERE condition to the ON clause. Otherwise, the WHERE condition would filter out NULL values, turning the LEFT JOIN into an inner join.
  • Adding a GROUP BY clause 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.

Comments