SQL: Get all records from one table AND a count of records from a second table?

Question

Say 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

The tie between the tables is the messageID field.

I would like one query that generates results like this, where I pull ALL the fields out of Table A, and a count of the number of comments for each message from Table B, like so:

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 have tried something like this:

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

but it doesn't work. Any ideas? It seems like it should be possible to do this in one query. I'm using MSSQL. Thanks for any help.

Answer 1

Scalar subquery will work:

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

As usual, there are a lot of ways to skin this cat, with varying performance profiles.

When using a GROUP BY, all columns in the output either need to be in the GROUP BY or in aggregate functions - even though there is no variation in the other columns within a messageID, they still would need to be in the 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