Do it with a subquery.

SELECT a.*, (
SELECT ISNULL(count(*), 0)
FROM b INNER JOIN c ON b.id = c.b_id
WHERE b.a_id = a.id) AS c_count
FROM
a

-Mike

>Hi,
>
>What is easiest way to accomplish the following?
>
>Straight Parent.Child relationsships A -> B -> C
>- records may or may not exist in B that belong to A (0-n)
>- records may or may not exist in C that belong to B (0-n)
>
>What I want is slick little select statement that will list ALL the 'A' records and a count of how many records exist downstream in 'C' for each 'A' record, whether that be 0,1, or whatever
>
>What is best way to accomplish this?
>
>TIA,