Блог

У меня есть 3 разные таблицы:

Сообщения, комментарии и аватар.

Что я хотел бы сделать, так это следующее: получить все сообщения в базе данных, подсчитывая для каждого сообщения комментарии и показывая аватар каждого пользователя.

Вот как создаются таблицы:

Сообщения -> messages_id, user_id, заголовок, сообщение

Комментарии -> comments_id, messages_id, комментарий

Аватар -> идентификатор аватара, идентификатор пользователя, аватар

Я попробовал следующее:

 SELECT *, COUNT(comments.comments_id) AS commentCount
FROM messages
LEFT JOIN comments ON messages.messages_id = comments.messages_id
LEFT JOIN avatar on messages.user_id = avatar.user_id
  

Но я получаю только одну строку для сообщений, остальное не видно.

Есть идеи, что я здесь делаю не так?

Вам необходимо использовать GROUP BY :

   SELECT messages_id
       , title
       , message
       , user_id
       , avatar_id
       , avatar
       , count(*) as commentCount
    FROM messages
   inner join avatar on messages.user_id = avatar.user_id
    left join comments on messages.messages_id = comments.messages_id
GROUP BY messages_id
  

Это должно сработать, если:

• messages_id является уникальным в messages
• user_id является уникальным в avatar

В противном случае вам нужно указать значение, которое вы хотите получить.

Отредактировано:

Я написал inner join для avatar табличного мышления, чтобы у всех пользователей был аватар. Если это не так, должно быть left join как в вашем запросе.

Вторая попытка

Возможно, ошибка заключалась в том, что group by должно быть messages.messages_id вместо messages_id . На самом деле в других RDMBS это ошибка:

ОШИБКА: ссылка на столбец «messages_id» неоднозначна

Я собираюсь быть более точным:

   SELECT m.messages_id    as id
       , min(m.title)     as title
       , min(m.message)   as message
       , min(m.user_id)   as user_id
       , min(a.avatar_id) as avatar_id
       , min(a.avatar)    as avatar
       , count(c.comments_id) as commentCount
    FROM messages as m
    left join avatar as a on m.user_id = a.user_id
    left join comments as c on m.messages_id = c.messages_id
GROUP BY m.messages_id
  

Все min они могут быть удалены в MySQL, если вы уверены, что существует только одно значение. В стандартном SQL вы должны выбрать нужное вам значение, если вы можете ввести только одно min или max .

Я меняю соединение с аватаром на be left join . Вероятно, не у всех пользователей есть аватар.