Postgres: multiple selects that exclude results from previous ones I have a table of widgets that have a number of connections with potentially overlapping modes. For instance, widget A may have 10 connections, and 5 of those can run in mode A and all 10 can run in mode B. A connection has at least one supported mode. I'm trying to figure out how to design a query that returns a list of devices that support a given number of connections with particular modes.

The simplified schema looks like this:

table widgets
  id
  name

table connections
  id
  widget_id

table modes_connections
  connection_id
  mode_id

table modes
  id
  name


I need to return widget_ids that satisfy filters similar to:

2 connections with mode A AND
2 connections with mode B AND
1 connection with mode C


I can't just join everything together because the first filter for mode A must exclude those results from the other filters, similarly the mode B filter must exclude those results from the mode C filter, etc. 

Also, I'm not sure how to prioritize results so connections with the least number of modes have preference. Consider the case where there are 3 connections that support modes A, B, C and two that support mode B. In the filter example above, the mode B filter should select the only-mode-B connections, allowing the A,B,C mode connections to satisfy the requirements for modes A and C. 

I'm totally at a dead end. Any suggestions or pointers would be appreciated. Redesigning the schema is also an option.
I'm stumped myself.  Is this what you're looking for?

select * from (
  select id, name, acnt, bcnt, ccnt,
         row_number()
         over (partition by id order by (acnt+bcnt+ccnt)) RN
    from (
      select w.id, w.name,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'A'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  ACNT,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'B'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  BCNT,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'C'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  CCNT
         from widgets w
    )
    where acnt &gt;= 2 and bcnt &gt;= 2 and ccnt &gt;= 1
) where RN = 1