У меня есть три стола:

Публикации:

   id  |  title
------------------
  1   |  post1
  2   |  post2
  3   |  post3
  4   |  post4
 

Комментарии:

   post_id  |  content
-----------------------
     1     |  asd
     1     |  dsad
     1     |  awtihaw
     2     |  aaaaa
     2     |  bbbbbbbb
     4     |  asdasd
 

Голосов:

   post_id  |  value
-----------------------
     1     |  1
     2     |  1
     2     |  -1
     3     |  1
     3     |  1
     3     |  1
     3     |  -1
 

Проблема

Мне нужно посчитать, сколько комментариев и сколько лайков у каждого поста.

Это мой вопрос:

 SELECT posts.id, COUNT(comments.post_id) as comments, SUM(votes.value) as votes
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id
LEFT JOIN votes ON posts.id = votes.post_id
GROUP BY posts.id
ORDER BY posts.id
 

И я на самом деле получаю результат, НО в результате говорится, что

сообщение с идентификатором 1 имеет 3 голоса и 3 комментария

где на самом деле у него есть только один голос и три комментария. Как правильно объединить три таблицы, чтобы они отображали правильный результат? Мне нужно уметь делать это только в запросах и желательно только в одном.

Одно простое решение использует коррелированные подзапросы:

 SELECT p.id,
       (SELECT COUNT(*)
        FROM comments c
        WHERE p.id = c.post_id
       ) as num_comments,
       (SELECT SUM(v.value)
        FROM votes v
        WHERE p.id = v.post_id
       ) as net_votes
FROM posts p
ORDER BY p.id;
 

С включенными индексами comments(post_id) , votes(post_id, value) , и posts(id) , это может быть самым быстрым решением.

Вы должны объединиться отдельно в comments и votes , а затем присоединиться к posts :

 SELECT p.id, c.comments_count, v.votes_sum
FROM posts p
LEFT JOIN (
  SELECT post_id, COUNT(post_id) comments_count
  FROM comments 
  GROUP BY post_id
) c ON p.id = c.post_id
LEFT JOIN (
  SELECT post_id, SUM(value) votes_sum
  FROM votes 
  GROUP BY post_id
) v ON p.id = v.post_id
ORDER BY p.id
 

Это будет возвращено NULL для сообщений, в которых нет комментариев или голосов.
Если вы хотите 0 вместо этого, используйте COALESCE() :

 SELECT p.id, 
       COALESCE(c.comments_count, 0) comments_count, 
       COALESCE(v.votes_sum, 0) votes_sum
FROM .....
 

Я думаю, что ПРИСОЕДИНИТЬСЯ-это не тот способ, которым мы должны пытаться.

Здесь, когда мы используем JOIN, комментарии к сообщению повторяются в результате для каждого голоса за один и тот же запрос. Аналогично голоса повторяются для каждого комментария.

В данном примере вы получили правильное значение только для комментариев, потому что у нас есть только одна строка в голосах за POST_ID 1.

Вместо СОЕДИНЕНИЯ мы должны использовать ВНУТРЕННИЙ запрос, чтобы получить правильный результат.