Getting data from three tables in one query

Question

I am trying to retrieve data from three tables at once. The tables look like: categories ``` id category messageid ``` messages ``` id title message ``` comments ``` id messageid message ``` What I am trying to get is 1 message (because I have a `WHERE` clause based on id `3`), 3 categories (because there are 3 categories linked to the message) and 2 comments (because there are 2 comments linked to the message). I'm trying to retrieve the data by usnig the following query: ``` SELECT categories.category, messages.id, messages.title, messages.message, comments.count, comments.id as commentid, comments.message as commentmessage FROM categories RIGHT JOIN (SELECT id, title, message FROM messages WHERE messaged.id = 3) messages ON messages.id = categories.messageid LEFT JOIN (SELECT count(id), id, message FROM comments GROUP BY id, message) comments ON messages.id = comments.messageid ORDER BY article.id DESC ``` However when running this query I get 6 results: ``` category id title messa…

Accepted Answer

There are a couple issues here as mentioned in the comments.

First, since you are joining three tables, the answer you are getting is correct. 1 x 2 x 3 rows = 6.

Second, your aggregate of the comments is not really aggregating anything. As you can see in your results, count is always one, when I expect you thought it would be 2 for two comments. Since you are grouping on the id, the count is performed for each unique id, which will always be one. I think you probably want to group on messageid

```
SELECT count(*), messageid
FROM comments
GROUP BY messageid
```

You would need to do another join or a separate query to get the comments themselves.

Also as discussed in the comments, you wouldn't typically get the information this way; you would usually just make the three queries since two of the relationships are one to many. You could, if your categories were short (and you're using SQL Server) compress the categories into their own column (ie. 'test, installation, question'). Here is how you would do that.

```
select id, title, message,
       (select CAST(category + ', ' as nvarchar(max))
        from @Categories c where messageid = m.id
        for xml path('')) as Categories
from @Messages m
where m.id = 3
```

Actually, there are several methods for doing that, but that's quick and dirty. Then you would only need one additional query for the comments. You could just join to the previous query and get all your information in two rows like this

```
select m.id, title, m.message,
       (select CAST(category + ', ' as nvarchar(max))
        from @Categories c where messageid = m.id
        for xml path('')) as Categories,
        cm.message
from @Messages m
left outer join @Comments cm on m.id = cm.messageid
where m.id = 3
```

but again, you'd probably just want to make an additional query to avoid duplicating information.

Finally, I wanted to show how you would probably want to do the comment count.

```
select m.id, title, m.message,
       (select CAST(category + ', ' as nvarchar(max))
        from @Categories c where messageid = m.id
        for xml path('')) as Categories,
        CommentCount,
        cm.message
from @Messages m
left outer join 
(   
    select messageid, COUNT(*) CommentCount
    from @Comments 
    group by messageid
) rsCommentCount on rsCommentCount.messageid = m.id
```

And lastly, here is a link showing that working.

Getting data from three tables in one query

Solution

Problem