SQL help: COUNT aggregate, list of entries and its comment count

Question

So, what I intended to do is to fetch a list of entries/posts with their category and user details, AND each of its total published comments. (entries, categories, users, and comments are separate tables)

This query below fetches the records fine, but it seems to skip those entries with no comments. As far as I can see, the JOINs are good (LEFT JOIN on the comments table), and the query is correct. What did I miss ?

SELECT entries.entry_id, entries.title, entries.content,
entries.preview_image, entries.preview_thumbnail, entries.slug,
entries.view_count, entries.posted_on, entry_categories.title AS category_title,
entry_categories.slug AS category_slug, entry_categories.parent AS category_parent,
entry_categories.can_comment AS can_comment, entry_categories.can_rate AS can_rate,
users.user_id, users.group_id, users.username, users.first_name, users.last_name,
users.avatar_small, users.avatar_big, users.score AS user_score, 
COUNT(entry_comments.comment_id) AS comment_count

FROM (entries)
JOIN entry_categories ON entries.category = entry_categories.category_id
JOIN users ON entries.user_id = users.user_id
LEFT JOIN entry_comments ON entries.entry_id = entry_comments.entry_id

WHERE `entries`.`publish` = 'Y'
AND `entry_comments`.`publish` = 'Y'
AND `entry_comments`.`deleted_at` IS NULL
AND `category` = 5

GROUP BY entries.entry_id, entries.title, entries.content,
entries.preview_image, entries.preview_thumbnail, entries.slug,
entries.view_count, entries.posted_on, category_title, category_slug,
category_parent, can_comment, can_rate, users.user_id, users.group_id,
users.username, users.first_name, users.last_name, users.avatar_big,
users.avatar_small, user_score

ORDER BY posted_on desc

edit: I am using MySQL 5.0

Answer 1

It's because you are setting a filter on columns in the entry_comments table. Replace the first with:

AND IFNULL(`entry_comments`.`publish`, 'Y') = 'Y'

Because your other filter on this table is an IS NULL one, this is all you need to do to allow the unmatched rows from the LEFT JOIN through.

Answer 2

Try changing the LEFT JOIN to a LEFT OUTER JOIN

OR

I'm no expert with this style of SQL joins (more of an Oracle man myself), but the wording of the left join is leading me to believe that it is joining entry_comments on to entries with entry_comments on the left, you really want it to be the other way around (I think).

So try something like:

LEFT OUTER JOIN entries ON entries.entry_id = entry_comments.entry_id

Answer 3

Well, you're doing a left join on entry_comments, with conditions:

`entry_comments`.`publish` = 'Y'
`entry_comments`.`deleted_at` IS NULL

For the entries with no comments, these conditions are false. I guess this should solve the problem:

WHERE `entries`.`publish` = 'Y'
AND (
        (`entry_comments`.`publish` = 'Y'
        AND `entry_comments`.`deleted_at` IS NULL)
    OR
        `entry_comments`.`id` IS NULL
    )
AND `category` = 5

In the OR condition, I put entry_comments.id, assuming this is the primary key of the entry_comments table, so you should replace it with the real primary key of entry_comments.