SQL tricks
September 17, 2022

Find items presents in each category in many-to-many relation

Assume we have 3 tables items, categories and item_categories where stored records about items, categories and relations between them

create table categories (
  id int generated always as identity primary key, 
  name varchar
);

insert into categories(name) 
values 
  ('Cat1'), 
  ('Cat2');

create table items (
  id int generated always as identity primary key, 
  name varchar
);

insert into items(name) 
values 
  ('Item1'), 
  ('Item2'), 
  ('Item3');

create table item_categories (
  item_id int references items(id), 
  category_id int references categories(id)
);

insert into item_categories 
values 
  (1, 1), 
  (2, 2), 
  (3, 1), 
  (3, 2);

How we can see, only Item3 presents in both categories, so we write query that should to give us Item3 as result.

select 
  i.name 
from items i 
join item_categories ic on i.id = ic.item_id 
join categories c on c.id = ic.category_id 
group by i.id, i.name 
having 
  count(distinct c.id) = (select count(*) from categories);

The above query joins all our table using foreign keys, group rows by items and compare count of different categories of each item to common categories count

sqlize