By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36253 in the last week).
create table widgets (id int, name text);
create table connections(id int, widget_id int);
create table modes_connections(connection_id int, mode_id int);
create table modes (id int, name text);
✓
✓
✓
✓
hidden batch(es)
insert into widgets values
(1, 'widget1'), (2, 'widget2');
insert into connections values
(1,1),
(2,1),
(3,1),
(4,1),
(5,2),
(6,2),
(7,2),
(8,2);
-- widget 1 has port_ids 1-4
-- ports 1,2 support modes A,B,C
-- ports 3,4 support mode B
-- widget 2 has port_ids 5-8:
-- ports 5,6 support mode B
-- ports 7,8 support modes A,B,C
insert into modes_connections values
(1,1),(1,2),(1,3),
(2,1),(2,2),(2,3),
(3,2),
(4,2),
(5,2),
(6,2),
(7,1),(7,2),(7,3),
(8,1),(8,2),(8,3);
insert into modes values
(1, 'A'),(2, 'B'),(3, 'C');
2 rows affected
8 rows affected
16 rows affected
3 rows affected
hidden batch(es)
select w.id, w.name, m.id mode_id, m.name mode_name, count(c.id) connections
from widgets w
join connections c
on c.widget_id = w.id
join modes_connections mc
on mc.connection_id = c.id
join modes m
on m.id = mc.mode_id
group by w.id, w.name, m.id, m.name
;
id
name
mode_id
mode_name
connections
1
widget1
1
A
2
1
widget1
2
B
4
1
widget1
3
C
2
2
widget2
1
A
2
2
widget2
2
B
4
2
widget2
3
C
2
…
hidden batch(es)
;with x as
(
select w.id, w.name, m.id mode_id, m.name mode_name, count(c.id) connections
from widgets w
join connections c
on c.widget_id = w.id
join modes_connections mc
on mc.connection_id = c.id
join modes m
on m.id = mc.mode_id
group by w.id, w.name, m.id, m.name
)
select id, name
from widgets wdg
where exists (select 1 from x where id=wdg.id and mode_id = 1 and connections >= 2)
and exists (select 1 from x where id=wdg.id and mode_id = 3 and connections >= 1)
-- here I'd expect widget1 and widget2, since both can support 2 ports of mode B and 2 ports of mode A