Duplicate data on multiple inner joins

Question

I have drawn a simple ERD:

The query I perform:

select s.*, count(c.store_id), count(p.store_id)
from store s
inner join customer c on c.store_id = s.id
inner join product p on p.store_id = p.id
group by s.id, s.store_name, s.address

The result:

id	store_name	address	count(c.store_id)	count(p.store_id)
1	store1	place	12	12
2	store2	place	4	4
3	store3	place	29	29
4	store4	place	9	9

Why do the counts give back the same number?

Every row has the same number for both count functions.

Did I mess up by joining customer and product on the same id field from store?

Answer 1

You can simply use correlated subqueries to count:

select s.*,
       (select count(*) from customer c where c.store_id = s.id),
       (select count(*) from product p  where p.store_id = s.id)
from store s

Alternatively, GROUP BY in derived tables before joining:

select s.*, c.c_count, p.p_count
from store s
left join (select store_id, count(*) c_count
           from customer
           group by store_id) c on c.store_id = s.id
left join (select store_id, count(*) p_count
           from product
           group by store_id) p on p.store_id = s.id