'SQL Query Returning Wring values for Sum and Count
My SQL Query contains three tables the posts table, post_likes table, and comments table. All tables are connected with the post_id primary key in the posts table. I am trying to return the content of the posts row as well the amount of likes/dislikes it has in the post_likes table, and the amount of comments. The query worked fine until I introduced the second left join and it now displays the like_count column x5 dislike_count column x5 and the new comment_count x4.
This is the query in question:
SELECT c.post_id, c.post_name, c.post_content, c.post_datetime, c.user_name, sum(p.like_count) AS like_count, sum(p.dislike_count) AS dislike_count, sum(s.comment_count) AS comment_count FROM posts c LEFT JOIN post_likes p ON c.post_id = p.post_id LEFT JOIN comments s ON c.post_id = s.post_id WHERE c.user_name = 'test' GROUP BY c.post_id
is returning the sum values:
//column | returned value | expected value
like_count | 10 | 2
dislike_count | 5 | 1
comment_count | 20 | 5
Some additional notes, the likes/dislikes are stored in the postlikes table with the structure.
post_like_id, like_count, dislike_count, post_id, user_name
The like or dislike count can only be 1 in either column the PHP handles this to ensure users cant like multiple times etc and the user_name column is the user who liked the post.
The comments table structure is as follows:
comment_id, comment_name, comment_content, comment_datetime, comment_count, post_id, user_name
The comment_count is always 1 when inserted to allow for the sum function, post_id is the id of the post for the comment, and the user_name is the user who commented.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
