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?

You're inner joining your tables, by definition the rows on the inner and outer side of the join must match, both are equivalent to `count(*)`. Simply remove the aggregation and examine the rows in the results and count the non-null values for each column... you won't find any null values. Why are you expecting them to be different, *what* are you counting? — Stu, May 16 '23 at 11:16
An ERD is an image of DDL. [Why should I not upload images of code/data/errors?](https://meta.stackoverflow.com/q/285551/3404097) When an image is appropriate: Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Don't use transparency because the site has both black & white background modes. Please in code questions give a [mre]. [ask] [Help] — philipxy, May 16 '23 at 11:18
You are asking for a store id to equal a product id. After you fix it you may have the problem [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097). But you don't say how the result should be a function of input. So we don't know what query expressions would be correct. — philipxy, May 16 '23 at 11:20

SelVazi · Answer 1 · 2023-05-16T13:16:47.647

2

on the second join, you are joining product with itself p.store_id = p.id

Simple data :

CREATE TABLE store (
  id int,
  store_name varchar(20),
  address varchar(20)
);

insert into store values
(1,'store1','address1'),
(2,'store2','address2'),
(3,'store3','address3');

CREATE TABLE product (
  product_name int,
  store_id int
);

insert into product values
(1,1),
(2,1);

CREATE TABLE customer (
  customer_name int,
  store_id int
);

insert into customer values
(1,2),
(2,2);

Then use LEFT JOIN, and COUNT(DISTINCT ...id) to eliminate duplicates :

To check if a store has customers/products :

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

Result :

id  store_name  address     count(c.store_id)   count(p.store_id)
1   store1      address1    0                   1
2   store2      address2    1                   0
3   store3      address3    0                   0

To get number of customers/products per store :

select s.*, count(DISTINCT c.customer_name) as number_customers, count(DISTINCT p.product_name) as number_products
from store s
left join customer c on c.store_id = s.id
left join product p on p.store_id = s.id
group by s.id, s.store_name, s.address

Result :

id  store_name  address     number_customers    number_products
1   store1      address1    0                   2
2   store2      address2    2                   0
3   store3      address3    0                   0

Demo here

edited May 16 '23 at 13:16

answered May 16 '23 at 11:16

SelVazi

10,028
2
13
29

Will not return what OP expects. – jarlh May 16 '23 at 11:27
Correct, with `inner join` not working, but with `Left join` is working – SelVazi May 16 '23 at 12:06
1

No. For a store with 50 customers and 100 products, the join will produce 50 x 100 = 5000 rows. If you count the IDs, no matter which, you get a count of 5000, because `COUNT(expression)` counts all rows for which the expression is not null. You can solve this with `COUNT(DISTINCT ...id)`, which will work, but this will still produce the huge intermediate result. Better go with jarlh's queries. – Thorsten Kettner May 16 '23 at 12:14
Thats correct @ThorstenKettner, I have edited my answer – SelVazi May 16 '23 at 12:18
1

You need to count distinct names, not distinct store_id's. See adjusted fiddle: https://dbfiddle.uk/IGuKeCSx – jarlh May 16 '23 at 13:01
Its depends on what the OP need ! .. I edited my answer to cover two possibilities since the question is not clear ! – SelVazi May 16 '23 at 13:18

jarlh · Accepted Answer · 2023-05-16T11:35:46.307

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

Duplicate data on multiple inner joins

2 Answers2