CODEWITHSUNDEEP
mysqlsql
Alon Dattner
Alon Dattner

Reputation: 215

How to properly join these three tables in SQL?

I'm currently creating a small application where users can post a text which can be commented and the post can also be voted (+1 or -1).

This is my database: enter image description here

Now I want to select all information of all posts with status = 1 plus two extra columns: One column containing the count of comments and one column containing the sum (I call it score) of all votes.

I currently use the following query, which correctly adds the count of the comments:

SELECT *, COUNT(comments.fk_commented_post) as comments
FROM posts 
LEFT JOIN comments 
ON posts.id_post = comments.fk_commented_post
AND comments.status = 1 
WHERE posts.status = 1
GROUP BY posts.id_post

Then I tried to additionally add the sum of the votes, using the following query:

SELECT *, COUNT(comments.fk_commented_post) as comments, SUM(votes_posts.type) as score
FROM posts 
LEFT JOIN comments 
ON posts.id_post = comments.fk_commented_post
AND comments.status = 1 
LEFT JOIN votes_posts
ON posts.id_post = votes_posts.fk_voted_post
WHERE posts.status = 1
GROUP BY posts.id_post

The result is no longer correct for either the votes or the comments. Somehow some of the values seem to be getting multiplied...

Upvotes: 0

Views: 58

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to join comments counts and votes counts to the posts. So, aggregate to get the counts, then join.

select
  p.*,
  coalesce(c.cnt, 0) as comments,
  coalesce(v.cnt, 0) as votes
from posts p
left join
(
  select fk_commented_post as id_post, count(*) as cnt
  from comments
  where status = 1
  group by fk_commented_post
) c on c.id_post = p.id_post
left join
(
  select fk_voted_post as id_post, count(*) as cnt
  from votes_posts
  group by fk_voted_post
) v on v.id_post = p.id_post
where p.status = 1
order by p.id_post;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

This is probably simpler using correlated subqueries:

select p.*,
       (select count(*)
        from comments c
        where c.fk_commented_post = p.id_post and c.status = 1
       ) as num_comments,
       (select sum(vp.type)
        from votes_posts vp
        where c.fk_voted_post = p.id_post
       ) as num_score       
from posts p
where p.status = 1;

The problem with join is that the counts get messed up because the two other tables are not related to each tother -- so you get a Cartesian product.

Upvotes: 1

Related Questions