Hi,

I have three tables :
- table item (id_item, title)
- table keyword (id_keyword, word)
- table item_keyword (id_item_keyword, id_item, id_keyword)

So, an item can be associated with 0 to n keywords, and a keyword with 0 to n items.
My purpose : finding the items who are associated with AT LEAST every keywords (id given).
Exemple : I'm giving as a parameter the ids 2,3,5. Item A is associated with keywords with ids 2,3,5,8 : it should be returned. Item B is associated with keywords ids 2,3,24,36 : it should not be returned.

If i make a simple join on id_item, and then an "IN" for the ids, it's an "OR" on the ids, i'd like an "AND".

SELECT DISTINCT it.id_item, it.title
FROM item it INNER JOIN item_keyword ik ON it.id_item = ik.id_item
WHERE ik.id_keyword IN (2,3,5)

Thanks

You wouldn't be able to accomplish this with one query since, as you pointed out, IN acts as an "OR" Operator and since your id_keywords are in different rows.

This may be a case for subqueries, but my experience with them is limited.

Well, the only way i've found for now is

SELECT id_item, title
FROM item WHERE
(id_item IN (SELECT id_item FROM item_motcle WHERE id_motcle = 2))
AND
(id_item IN (SELECT id_item FROM item_motcle WHERE id_motcle = 3))
AND
(id_item IN (SELECT id_item FROM item_motcle WHERE id_motcle = 5))

I also found a way to make it with inner join, but it's much less readable :

SELECT DISTINCT it.id_item, it.title
FROM item it INNER JOIN item_motcle a ON it.id_item = a.id_item
INNER JOIN item_motcle b ON a.id_item = b.id_item
INNER JOIN item_motcle c ON b.id_item = c.id_item
WHERE a.id_motcle = 2 AND b.id_motcle = 3 AND c.id_motcle = 5