Let us say I've two tables:

TABLE A

MessageID Message

1 Hello
2 Bonjour

etc..

TABLE B

CommentID MessageID Comment

1 2 This can be a comment to a person saying Bonjour
2 2 This really is another comment to Bonjour

What I am attempting to do operates one query that pulls all of the records from Table A ("the messages") together with a count of all of the comments for every message from Table B.

The end result could be:

Hello - comments
Bonjour - 2 comments

I understand this really is most likely some mixture of utilizing a join having a count(*), however i can't appear hitting on the perfect syntax.

Any ideas?

For any message based approach:

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

You will want a LEFT JOIN to incorporate records in Table A that haven't any comments in Table B.

Give mtss is a try:

SELECT a.MessageID, COUNT(*)
FROM TABLEA a
JOIN TABlEB b
ON b.MessageID = a.MessageID
GROUP BY a.MessageID

Something similar to this:

SELECT MessageID, COUNT(CommentID)
FROM "TABLE B"
GROUP BY MessageID

If you want Message additionally to MessageID, you need to have the ability to make a move such as this:

SELECT MessageID, MIN(Message), COUNT(CommentID)
FROM "TABLE A" LEFT JOIN "TABLE B" USING (MessageID)
GROUP BY MessageID

(Oracle syntax, most likely same for MSSQL)

Just an addendum to what's already published since, if you are by using this for any scripting language, you will probably need named posts for my way through the Choose list:

SELECT tableA.messageID, message, count(commentID) AS commentCount
FROM tableA LEFT JOIN tableB ON tableA.messageID = tableB.messageID 
GROUP BY message