clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36226 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, m.name, count(c.id) cnt 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.connection_id group by w.id, w.name, m.id, m.name ;
id name id name cnt
1 widget1 1 A 3
1 widget1 2 B 3
1 widget1 3 C 1
 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 = 2 and connections = 2) -- here I'd expect widget1 and widget2, since both can support 2 ports of mode B and 2 ports of mode A
id name
 hidden batch(es)