CODEWITHSUNDEEP
sqlpostgresql
Luffydude
Luffydude

Reputation: 772

Joining and counting rows from different tables

So for example I need to create a report view that associates stuff to an person. Let's say properties and dogs. There is no connection between B and C

table person

table properties

table dogs

To tally up stuff, my report looks like:

select a.id, a.name, count(b.*), count(c.*) 
from person a 
  left join properties b on a.name = b.person 
  left join dogs c on a.name = c.person;

Intended outcome would be say for person A to own 10 properties and 20 dogs.

Somehow this join is completely multiplying the number of properties and saying the right number of dogs. How to fix the join?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The quick-and-dirty method is to use count(distinct):

select a.id, a.name, count(distinct b.id), count(distinct c.id)
from table_a a left join
     table_b
     on a.name = b.person left join
     table_c c
     on a.name = c.person
group by a.id, a.name;

Using a lateral join or subquery is probably faster -- particularly if there are lots of rows in the two tables:

select a.id, a.name,
       (select count(*)
        from b
        where a.name = b.person
       ),
       (select count(*)
        from c
        where a.name = c.person
       ),
from table_a a ;

By the way, if table a has an id, that should be used for linking to other tables rather than the name.

Upvotes: 2

Related Questions