How to perform left joins on multiple tables along with aggregate functions-CodePudding

SELECT 
posts.id,posts.user_id,
username, title,posts.body as post_body, posts.created_at, posts.is_open, posts.views as views, users.photoUrl,
COUNT(DISTINCT answers.id) as answer_count,
COUNT(DISTINCT comments.id) as comment_count,
COUNT (CASE answers.approved WHEN 1 THEN 1 ELSE null END) as correct_count,
GROUP_CONCAT(tagname) as tags
FROM posts 
LEFT JOIN posttag ON posts.id = post_id 
JOIN tags ON tag_id = tags.id 
JOIN users ON user_id = users.id 
LEFT JOIN answers ON answers.post_id = posts.id 
LEFT JOIN comments ON posts.id = comments.post_id 
WHERE posts.id = ?;

I am trying to query every post with its corresponding answers, comments and tags.

However in my query result, I get 8 tags when there are 4

The COUNT correct_count changes from from 1 to 4

How can I get the correct data?

CodePudding user response：

Use DISTINCT for correct_count and tags also:

.....................................
COUNT(DISTINCT CASE WHEN answers.approved THEN answers.id END) as correct_count,
GROUP_CONCAT(DISTINCT tagname) as tags