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

Solution 1:

Scalar subquery will work:

SELECT tableA.*
    ,(SELECTcount(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.

Solution 2:

You can use CTE for the same.

;WITH CTE_MessageCount (MessageId, Count)
AS
(
SELECT MessageId, Count(*) FROM TableB GROUPBY MessageId
) 

SELECT A.*, T.*
FROM tableA A JOIN CTE_MessageCount T ON A.messageID = T.MessageID

Solution 3:

Try this query:

SELECT a.*, b.msgCount
  FROM tableA a LEFT JOIN 
    ( SELECT messageID, COUNT(1) AS msgCount FROM tableB b GROUPBY messageID) b
        ON a.messageID =  b.messageID