Question 169665 in PostgreSQL Question posted in
The official documentation can be found here.

Can multiple inner joins duplicate data in Postgresql?

I have drawn a simple ERD:

enter image description here

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search