Oracle return rows only if all join conditions match

Viewed 208

http://sqlfiddle.com/#!4/bab93d

See the SQL Fiddle example... I have Customers, Tags, and a mapping table. I am trying to implement customer search by tags, and it has to be an AND search. The query is passed a list of tag identifiers (any number), and it has to return only customers that have ALL the tags.

In the example I have used an IN operator, but that corresponds to an OR search and doesn't solve my problem. What should the query look like to be an AND search?

select
  *
from
  customer c
  inner join customer_tag ct on ct.customer_id = c.customer_id
where
  ct.tag_id in (1, 2);

This returns both customers, but only the first customer is tagged with tag 1 and 2.

4 Answers
Related