views:

20

answers:

1

I have a table relationship which links one person to many relatives. so the tables are 1. Client. 2. Client_relative. I want to display all the rows of the Persons table, while displaying a count of how many relatives each person has. I have this query: SELECT c.clientid, c.fname, c.lname, count(cr.relativeid) as relativecount FROM {client} AS c INNER JOIN {client_relative} cr on c.clientid = cr.clientid

This isn't working. Any ideas? Thank you.

+1  A: 
select c.*, cc.relativecount
from client c
inner join (
    select clientid, count(*) as relativecount  
    from client_relative
    group by clientid 
) cc on c.clientid = cc.clientid
RedFilter
Wait a minute... I swear I saw your post doing the join before the group by. But there's no edit history... strange.
Emtucifor
Thanks Red! that worked
berto77
Hi Red, actually, I notice that if the client doesn't have a relative, this query doesn't output the client. It just ignores those that don't have relatives. Any ideas? Thanks-
berto77
OK, I replaced the INNER JOIN with a LEFT JOIN and this solved the issue.
berto77
@berto, yes, that is the way to handle that. You can change `cc.relativecount` to `ISNULL(cc.relativecount, 0)` to get slightly nicer output in your `SELECT` clause.
RedFilter