title: SQL: Get all records from one table AND a count of records from a second table?
body: <p>Say there are two tables:</p>
  
  <p><strong>TABLE A</strong></p>
  
  <pre><code>messageID / Message                     / More..
  1         / This is the first message   / Etc..
  2         / This is the second message  / Etc..
  3         / This is the third message   / Etc..
  </code></pre>
  
  <p><strong>TABLE B</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>The tie between the tables is the <strong>messageID</strong> field.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have tried something like this:</p>
  
  <pre><code>SELECT tableA.*, count(commentID) as commentcount 
  FROM tableA LEFT JOIN tableB ON tableA.messageID = tableB.messageID GROUP BY messageID
  </code></pre>
  
  <p>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.</p>