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