Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 2


select s.*, count(DISTINCT c.store_id) as has_customers, count(DISTINCT p.store_id) as has_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


id
store_name
address
has_customers
has_products


1
store1
address1
0
1

2
store2
address2
1
0

3
store3
address3
0
0



SELECT 3


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


id
store_name
address
number_customers
number_products


1
store1
address1
0
2

2
store2
address2
2
0

3
store3
address3
0
0



SELECT 3