SQL getting data from multiple tables

Question

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?

DavidEG · Accepted Answer · 2011-10-08 07:39:42Z

1

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.

edited Oct 8, 2011 at 7:39

answered Oct 7, 2011 at 22:04

DavidEG

5,9673 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

moonwalker Over a year ago

Thanks David for your reply. But I still get only one message instead of the 2 messages I have in my database. Messages_id is indeed unique in messages and user_id is unique in avatar. Maybe I need to specify that not all messages have comments.

DavidEG Over a year ago

@moonwalker check the second query I wrote. If doesn't work would be helpful to have an example of table data, expected output and real output.

moonwalker Over a year ago

Thanks a lot David. Your second solution works great. I'm going to study it and try to understand what I did wrong.

Collectives™ on Stack Overflow

SQL getting data from multiple tables

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related