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
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