I'm on Postgres 11 and I have these tables

 foo              foobar

| id |      | foo_id | bar_id |
------      -------------------
|  a |      |      a |      1 |
|  b |      |      a |      2 |
|  c |      |      b |      2 |
|  d |      |      b |      3 |

And I need all bar_id which are common to all foos. So I have this query:

select b.bar_id from foo f join foobar b on b.foo_id = f.id

which gives me


while I'd need just 2, i.e. the intersection of the result set partitioned by f.id. Then, I might apply a where clause, e.g. where f.id in (...). How can I achieve this?

  • There isn't a foobar ('c',2) and ('d',2) – McNets May 30 at 14:18
  • What do you mean "all bar_id which are common to all foo"? Your sample data has no rows of foobar common for all foo - c and d aint represented in the foobar – Kondybas May 30 at 15:47
  • I oversimplified my example, I'll prepare a more real one! – Giovanni Lovato May 30 at 16:44

It seems that your desired result do not use all foo values, there is no ('c',2) and ('d',2), then you can remove it from the equation and use only foobar table.

group by 
    count(fooid) = (select count(distinct fooid) from foobar);
| barid |
| ----: |
|     2 |

db<>fiddle here

  • Thank you! The query comes from an ORM library which needs foo as the query root (maybe I oversimplified the example); neat trick with having count! Any higher level semantic to avoid the subquery comes to your mind? Also Postgres specific would work. – Giovanni Lovato May 30 at 14:30
  • Why should foo be the query root? The problem is to find "all bar ids which ..." – ypercubeᵀᴹ May 30 at 15:41
  • For many other ways to accomplish this, see: How to filter SQL results in a has-many-through relation – ypercubeᵀᴹ May 30 at 15:44
  • Thank you @ypercubeᵀᴹ that answer lead me to the For an unknown number of properties which solves my issue similarly as McNets suggested but with the level of abstraction I need (i.e. without the group by on the main query). – Giovanni Lovato May 30 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.