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);

insert into widgets values (1, 'widget1'), (2, 'widget2'),(3, 'widget3'),(4, 'widget4'); insert into connections values (1, 1),(2, 1),(3, 1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1), (11, 2),(12, 2),(13, 3),(14, 3),(15, 4); insert into modes_connections values (1, 1),(1, 2),(1, 3),(1, 4),(1, 5), (2, 1),(2, 2),(2, 3),(2, 4),(2, 5), (2, 6),(2, 7),(2, 8),(2, 9),(2, 10), (3, 5),(3, 6),(3, 7),(3, 8),(3, 9); insert into modes values (1, 'A'),(2, 'B'),(3, 'C'),(4, 'D');

select w.id, w.name, m.id, m.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 ;

;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)