SQL:多对多关系，IN条件

我有一个名为Transactions的表，该表通过items_transactions表与项目具有多对多关系。

我想做这样的事情:

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>))

但这给了我所有与列表中一个或多个项目相关联的交易，而我只希望它给我与所有这些项目相关联的交易。

任何帮助，将不胜感激。

最佳答案

您必须扩展查询列表中的所有项目:

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>)
...

您也许还可以使用IN和COUNT DISTINCT来按摩它，我不确定哪个会更快。类似于(完全未经测试)的内容:

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

关于SQL:多对多关系，IN条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2965035/