How to add condition on multiple-join table

Question

Hi, I have those two tables:

client:
  id (int) #PK
  name (varchar)

client_category:
  id (int) #PK
  client_id (int)
  category (int)

Let's say I have those datas:

client: {(1, "JP"), (2, "Simon")}
client_category: {(1, 1, 1), (2, 1, 2), (3, 1, 3), (4,2,2)}

tl;dr client #1 has category 1, 2, 3 and client #2 has only category 2

I am trying to build a query that would allow me to search multiple categories. For example, I would like to search every clients that has at least category 1 and 2 (would return client #1). How can I achieve that?

Thanks!

Answer 1

select client.id, client.name
from client
inner join client_category cat1 on client.id = cat1.client_id and cat1.category = 1
inner join client_category cat2 on client.id = cat2.client_id and cat2.category = 2

Answer 2

the "dumb" answer

select c.id
  from client c
 where c.id in (select cc.client_id from client_category cc where cc.id = 1)
   and c.id in (select cc.client_id from client_category cc where cc.id = 2)