Let us say I've two tables:
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.
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