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.
Many-to-Many Relationships You can also represent many-to-many data relationships in an SQL repository. For example, an author may have written multiple books, and a book may have multiple authors. This example uses three tables, author, book, and author_book.
One-to-Many is the most commonly used relationship among tables. A single record from one table can be linked to zero or more rows in another table. Let's take an example of the Employee and Address table in the HR database. The Employee table stores employee records where EmployeeID is the primary key.
You can also represent many-to-many data relationships in an SQL repository. For example, an author may have written multiple books, and a book may have multiple authors. Representing this kind of relationship depends on the type="multi" attribute in a <table> tag.
A many-to-many (or M:N) relationship is one of the three database relationships. The other two are: By definition, a many-to-many relationship is where more than one record in a table is related to more than one record in another table.
You have to expand out your query for all of the items in the list:
SELECT "transactions".*
FROM "transactions"
WHERE EXISTS (SELECT 1 FROM "items_transactions"
INNER JOIN "items" ON "items".id = "items_transactions".item_id
WHERE "items_transactions".transaction_id = "transactions".id
AND "items".id = <first item in list>)
AND EXISTS (SELECT 1 FROM "items_transactions"
INNER JOIN "items" ON "items".id = "items_transactions".item_id
WHERE "items_transactions".transaction_id = "transactions".id
AND "items".id = <second item in list>)
...
You might also be able to massage it out using IN
and COUNT DISTINCT
, I'm not sure which would be faster. Something like (completely untested):
SELECT "transactions".*
FROM "transactions"
INNER JOIN (SELECT "items_transactions".transaction_id
FROM "items_transactions"
INNER JOIN "items" ON "items".id = "items_transactions".item_id
WHERE "items".id IN (<list of items>)
GROUP BY "items_transactions".transaction_id
HAVING COUNT(DISTINCT "items".id) = <count of items in list>) matches ON transactions.transaction_id = matches.transaction_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With