SQL从多个表中获取数据 [英] SQL getting data from multiple tables

问题描述

我有3个不同的表：

消息，评论和头像。

我想做什么执行以下操作：
获取数据库中的所有消息，为每条消息计数注释并显示每个用户的头像。

这是表格的方式内置：

消息-> messages_id，user_id，标题，消息

注释-> comments_id，messages_id，评论

头像-> avatar_id，user_id，头像

我尝试了以下操作：

  SELECT *，COUNT（comments.comments_id）AS commentCount 
来自消息
左加入对message.messages_id = comments.messages_id的评论
message.user_id = avatar.user_id的左联接头像
  

但是我只得到一行

任何人知道我在这里做错了吗？

解决方案

您需要使用 GROUP BY ：

 选择messages_id 
，title 
，message 
，user_id 
，avatar_id 
，头像
，count（*）作为commentCount 
 FROM消息
 messages上的内部加入头像。user_id= avatar.user_id 
留下对消息的加入评论.messages_id = comments.messages_id 
 GROUP BY messages_id 
  

在以下情况下应该可以使用：

• messages_id 在 messages 中是唯一的


• user_id 在头像

中是唯一的

否则，您需要指定要获取的值。

编辑后：

我为化身表写了内部联接，认为所有用户都有一个化身。如果不正确，应像在查询中那样左联接。

第二次尝试

也许错误是 group by 的分组应为 messages.messages_id 而不是 messages_id 。实际上，在其他RDMBS中，这是一个错误：

错误：列引用 messages_id不明确

我会更加精确：

 选择m。 messages_id作为id 
，min（m.title）作为标题
，min（m.message）作为message 
，min（m.user_id）作为user_id 
，min（a .avatar_id）作为avatar_id 
，min（a.avatar）作为avatar 
，count（c.comments_id）作为commentCount 
 FROM消息作为m 
左起加入avatar作为m .user_id = a.user_id 
在m.messages_id = c.messages_id 
上以c身份留下了加入注释，m.messages_id 
  

如果您确定只有一个值，可以在MySQL中删除所有 min 。在标准SQL中，您必须选择所需的值，如果只有一个，则可以键入 min 或 max 。 / p>

我将带有头像的联接更改为左联接。也许并非所有用户都具有化身。

I have 3 different tables:

Messages, Comments and Avatar.

What I would like to do is the following: Get all Messages in the database, counting for each message the comments and showing the avatar of each user.

This is how the tables are built:

Messages -> messages_id, user_id, title, message

Comments -> comments_id, messages_id, comment

Avatar -> avatar_id, user_id, avatar

I tried the following:

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

But I get only one row for the messages, the rest is not visible.

Any idea what I'm doing wrong here?

解决方案

You need to use the 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

This should work if:

• messages_id is unique in messages
• user_id is unique in avatar

Otherwise you need to specify the value you want to get.

Edited:

I wrote inner join for avatartable thinking in that all users have an avatar. If this is not true should be left join like in your query.

Second try

Maybe the error was that the group by should be messages.messages_id instead of messages_id. In fact in others RDMBS this is an error:

ERROR: column reference "messages_id" is ambiguous

I'm going to be more precise:

  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

All the mincould be deleted in MySQL if you are sure there is only one value. In standard SQL you must choose the value you want, if there is only one you can type min or max.

I change join with avatar to be left join. Probably not all users have an avatar.

这篇关于SQL从多个表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！