CODEWITHSUNDEEP
sqldrupalinner-join
berto77
berto77

Reputation: 895

Performing select count across two tables sql

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?

Upvotes: 0

Views: 349

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171599

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

Upvotes: 1

Related Questions