By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table employee (
id int,
name varchar(50),
age int
);
create table services (
id int,
title varchar(50)
);
create table empl_services (
employee_id int,
service_id int
);
insert into employee (id, name, age) values
(1, 'Петя', 23),
(2, 'Игорь', 43),
(3, 'Вася', 32),
(4, 'Коля', 45),
(5, 'Альберт', 38);
insert into services (id, title) values
(1, 'Крутит гайки'),
(2, 'Крутит болты'),
(3, 'Носит ящики'),
(4, 'Пилит фанеру'),
(5, 'Крутит сваи'),
(6, 'Гнёт арматуру');
insert into empl_services (employee_id,service_id) values
(1,1),
(1,5),
(1,3),
with
s as (
select id from services where title in ('Крутит гайки','Носит ящики')
),
r as (
select employee_id from empl_services es
join s on s.id = es.service_id
group by employee_id
having count(employee_id) = (select count(1) from s)
)
select *
from employee e
join r on r.employee_id = e.id
;
| id | name | age | employee_id |
|---|---|---|---|
| 1 | Петя | 23 | 1 |
| 4 | Коля | 45 | 4 |