ajxs ajxs - 3 years ago 127
SQL Question

Issue counting joined rows from two tables

My schema, query, and problematic results can be seen here:
http://sqlfiddle.com/#!17/55bc3/5/0

I've created a schema for storing posts, comments, and favourites. ( I've simplified my example for the sake of demonstration ). I'm trying to write a query to aggregate the like/favourite counts for each post, for display on a 'front page'.

To model the relationships between users/posts/favourites I've used multiple intersection tables. In the query I'm using two

LEFT JOIN
s, and then
COUNT
ing distinct columns in the results. I've encountered an issue where the
COUNT
I'm storing as
comment_count
overrides
favourite_count
when it returns anything above 0, causing it to return duplicate values for both columns.

I think I understand the mechanism behind this, being that the
GROUP
ing of the results is causing the resulting rows to get squashed together to yield an incorrect result. I was wondering if anyone could let me know some of the theory behind what this is called, and how you would correctly write queries to handle this scenario.

Answer Source

As they are unrelated tables, you can count individually and then join.

SELECT p.id
,coalesce(c.comment_count,0) as comment_count
,coalesce(f.favorite_count,0) as favorite_count
FROM post p
LEFT JOIN (select post_id,count(*) as comment_count 
           from comment group by post_id) c ON c.post_id=p.id
LEFT JOIN (select post_id,count(*) as favorite_count 
           from favourite group by post_id) f ON f.post_id=p.id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download