Performing select count across two tables sql

Question

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?

D'Arcy Rittich · Accepted Answer · 2010-09-07 20:42:27Z

1

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

answered Sep 7, 2010 at 20:42

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ErikE Over a year ago

Wait a minute... I swear I saw your post doing the join before the group by. But there's no edit history... strange.

berto77 Over a year ago

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 Over a year ago

OK, I replaced the INNER JOIN with a LEFT JOIN and this solved the issue.

D'Arcy Rittich Over a year ago

@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.

Collectives™ on Stack Overflow

Performing select count across two tables sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related