Join and read in SQL Server

Question

I have two tables; call them in TableA and TableB. Each element of TableB is associated with an element of table A in relationship. Some TableA elements may not have a corresponding element in TableB.

I need to select each element in tableA together with a column that for each row in the result set will contain the number of elements in tableB that are associated with this row in tableA and that have a specific property.

Using MS SQL Server 2008 (must also work in SQL Server 2005).

Answer 1

Update

SELECT A.elementid, A.column1, A.column2, A.column3, COUNT(CASE WHEN B.someColumn > 0 THEN B.elementid ELSE NULL END) Q FROM TableA A LEFT JOIN TableB B ON A.elementid = B.elementid GROUP BY A.elementid, A.column1, A.column2, A.column3 

Answer 2

 SELECT TableA.MyColumn, COUNT(TableB.SomeColumn) AS MyCount FROM TableA LEFT OUTER JOIN TableB ON TableA.TableAKeyColumn = TableB.TableAKeyColumn GROUP BY TableA.MyColumn