SQL: many-to-many relationship, IN condition
- by Maarten
I have a table called transactions with a many-to-many relationship to items through the items_transactions table.
I want to do something like this:
SELECT "transactions".*
FROM "transactions"
INNER JOIN "items_transactions" ON "items_transactions".transaction_id = "transactions".id
INNER JOIN "items" ON "items".id = "items_transactions".item_id
WHERE (items.id IN (<list of items>))
But this gives me all transactions that have one or more of the items in the list associated with it and I only want it to give me the transactions that are associated with all of those items.
Any help would be appreciated.
