ajxs ajxs - 3 years ago 144
SQL Question

Issue counting joined rows from two tables

My schema, query, and problematic results can be seen here:

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

s, and then
ing distinct columns in the results. I've encountered an issue where the
I'm storing as
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
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.

,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