CODEWITHSUNDEEP
sqlpostgresqljoincountschema
ajxs
ajxs

Reputation: 3638

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 JOINs, and then COUNTing 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 GROUPing 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.

Upvotes: 0

Views: 39

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

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

Upvotes: 1

Related Questions