SQL: get all records from one table and count records from second table?

Question

Let's say that there are two tables:

TABLE A

messageID / Message / More.. 1 / This is the first message / Etc.. 2 / This is the second message / Etc.. 3 / This is the third message / Etc.. 

TABLE B

 commentID / messageID / Comment 1 / 2 / This is a comment to the second message 2 / 2 / This is another comment to the second message 3 / 3 / This is a comment to the third message 

A table relationship is a messageID field.

I need one query that generates such results when I get ALL the fields from table A and count the number of comments for each message from table B, for example:

 messageID / Message / More... / CommentCount 1 / This is the first message / etc... / 0 2 / This is the second message / etc... / 2 3 / This is the third message / etc... / 1 

I tried something like this:

 SELECT tableA.*, count(commentID) as commentcount FROM tableA LEFT JOIN tableB ON tableA.messageID = tableB.messageID GROUP BY messageID 

but that will not work. Any ideas? It seems like this should be possible to do in one request. I am using MSSQL. Thanks for any help.

Answer 1

The scalar subquery will work:

 SELECT tableA.* ,(SELECT count(commentID) FROM tableB WHERE tableA.messageID = tableB.messageID) as commentcount FROM tableA 

As usual, there are many ways to throw this cat with different performance profiles.

When using GROUP BY all output columns must be in GROUP BY or in aggregate functions - although there are no changes in other columns inside the message identifier, they should still be in GROUP BY .

Answer 2

You can use CTE for the same.

 ;WITH CTE_MessageCount (MessageId, Count) AS ( SELECT MessageId, Count(*) FROM TableB GROUP BY MessageId ) SELECT A.*, T.* FROM tableA A JOIN CTE_MessageCount T ON A.messageID = T.MessageID 

Answer 3

Try this query:

 SELECT a.*, b.msgCount FROM tableA a LEFT JOIN ( SELECT messageID, COUNT(1) AS msgCount FROM tableB b GROUP BY messageID) b ON a.messageID = b.messageID