Oracle return rows only if all join conditions match

Question

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.

Answer 1

You could use correlated subquery to get list of all customers:

SELECT  *
FROM  customer c
WHERE c.customer_ID IN
(
     SELECT customer_id
     FROM customer_tag ct
     WHERE ct.customer_id = c.customer_id
       AND ct.tag_id IN (1,2)
     GROUP BY customer_id
     HAVING COUNT(DISTINCT tag_id) = 2
);  

LiveDemo

It is easy to extend just:

WHERE ct_tag IN (1,2,3)
...
HAVING COUNT(DISTINCT tag_id) = 3