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

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.

---

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.

---

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

---

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