Postgres: Match mutiple values in a join table

Question

I have a table post

POST TABLE
id | name
 1 | post1
 2 | post2

I have a join table post category

POST CATEGORY (JOINT TABLE)
id | post_id  | post_category_id
 1 |       1  |               10
 2 |       1  |               11
 3 |       2  |               11

How would I select posts that have BOTH post_category_id 10 and 11 ?

Wanted Result :

POST TABLE
id | name
 1 | post1

Answer 1

One method uses exists:

select p.*
from posts p
where exists (select 1
              from postcategory pc
              where pc.post_id = p.id and pc.category = 10
             ) and
      exists (select 1
              from postcategory pc
              where pc.post_id = p.id and pc.category = 11
             ) ;

If you just wanted the ids, I would suggest aggregation:

select pc.post_id
from postcategory pc
where pc.category in (10, 11)
group by pc.post_id
having count(*) = 2;  -- use count(distinct category) if the table can have duplicates

Of course, you can join in posts and use this method as well.