If you have orphans, then change LEFT JOIN to FULL OUTER JOIN. You'll get counts for child ID which don't have corresponding parents. In other words, the record with Parent.PK = NULL and count will show you have orphans.
>Hi Borislav. Thanks fro replying.
>I know, in an ideal world this should not be the case, but I wanted to cover all eventualities, in case there were any
>orpjaned childen
>Regards,
>Gerard
>
>>>I have two tables, one is a child of the other.
>>>Is there one Sql Statement that will give me a count of the child records for each Primary Key
>>>e.g.
>>>Parent Table
>>>A
>>>B
>>>C
>>>--------------------------------
>>>Child Table
>>>A XX
>>>A VVV
>>>A GDS
>>>B HH
>>>B UYU
>>>D aaaa
>>>-----------------------------
>>>Result would be
>>>A 3
>>>B 2
>>>C 0
>>>D 1
>>>
>>>Tia
>>>Gerard
>>
>>
>>SELECT Parent.PK, ISNULL(COUNT(*), 0) AS ChildRecords
>>FROM Parent
>>LEFT JOIN Child ON Parent.PK = Child.FK
>>GROUP BY Parent.PK
>>
>>
>>BTW How did you have a Child records iof the Parent record did not exists?
If it's not broken, fix it until it is.
My Blog