skills, PostgreSQL

Language: Layout:

create table work_units (id integer, name text);
create table work_units_skills (work_unit_id integer, skill_id integer);
create table workers_skills (worker_id integer, skill_id integer);

insert into work_units 
  (id, name) 
values 
  (1, 'unit 1'),
  (2, 'unit 2'),
  (3, 'unit 3'),
  (4, 'unit 4'),
  (5, 'unit 5');

insert into work_units_skills 
  (work_unit_id, skill_id) 
values 
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 3),
  (2, 4),
  (2, 5),
  (3, 3),
  (4, 5),
  (4, 6);

insert into workers_skills 
  (worker_id, skill_id) 
values 
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 3),
  (2, 5),
  (3, 5),
  (4, 1),
  (5, 1),
  (5, 2),
  (5, 3),
  (5, 5),
  (5, 6);

-- Show the required skills for each work_unit
select work_unit_id, array_to_string(array_agg(skill_id), ',') as required_skills
from work_units_skills
group by work_unit_id
order by work_unit_id;

-- Work units for worker 1
select *
from work_units
where id in (select work_unit_id
             from work_units_skills
             group by work_unit_id
             having array_agg(skill_id) <@ array(select skill_id from workers_skills where worker_id = 1));

-- Work units for worker 5
select *
from work_units
where id in (select work_unit_id
             from work_units_skills
             group by work_unit_id
             having array_agg(skill_id) <@ array(select skill_id from workers_skills where worker_id = 5));


absolute service time: 0,49 sec