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