Mysql: multiple connection problem

Question

I am trying to select a table with several joins, one for the number of comments using COUNT and one to select the total value for voting using SUM, the problem is that these two joins influence each other and not show:

3 votes 2 comments

I get 3 * 2 = 6 votes and 2 * 3 comments

This is the query I'm using:

SELECT t.*, COUNT(c.id) as comments, COALESCE(SUM(v.vote), 0) as votes
FROM (topics t)
LEFT JOIN comments c ON c.topic_id = t.id
LEFT JOIN votes v ON v.topic_id = t.id
WHERE t.id = 9

Answer 1

What you do is SQL Antispam, which I call Goldberg Machine . Why put the problem much more complicated by forcing it to execute in a single SQL query?

Here is how I would really solve this problem:

SELECT t.*, COUNT(c.id) as comments
FROM topics t
LEFT JOIN comments c ON c.topic_id = t.id
WHERE t.id = 9;

SELECT t.*, SUM(v.vote) as votes
FROM topics t
LEFT JOIN votes v ON v.topic_id = t.id
WHERE t.id = 9;

, . , , ? .

Answer 2

SELECT t.*, COUNT(c.id) as comments, COALESCE(SUM(v.vote), 0) as votes
FROM (topics t)
LEFT JOIN comments c ON c.topic_id = t.id
LEFT JOIN votes v ON v.topic_id = t.id
WHERE t.id = 9
GROUP BY t.id

, ,

SELECT `topics`.*,
(
    SELECT COUNT(*)
    FROM `comments`
    WHERE `topic_id` = `topics`.`id`
) AS `num_comments`,
(
    SELECT IFNULL(SUM(`vote`), 0)
    FROM `votes`
    WHERE `topic_id` = `topics`.`id`
) AS `vote_total`
FROM `topics`
WHERE `id` = 9

Answer 3

SELECT t.*, COUNT(DISTINCT c.id) as comments, COALESCE(SUM(v.vote), 0) as votes
FROM (topics t)
LEFT JOIN comments c ON c.topic_id = t.id
LEFT JOIN votes v ON v.topic_id = t.id
WHERE t.id = 9