asp.net - T-SQL Select and count from different table?

Question

I have a table (Threads) containing a field (id). I would like to select every row from Threads, as well as the number of rows in the table Posts where the field Posts.thread is the same as Threads.id.

How can this be done in SQL?

(Something like this pseudo-SQL: SELECT *, COUNT(* FROM Posts WHERE Posts.id=Threads.id) FROM Threads)

Answer 1

Sure - something like this?

SELECT 
    t.ThreadID,
    (SELECT COUNT(*) FROM dbo.Posts p WHERE p.ThreadID = t.ThreadID)
FROM
    dbo.Threads t