CODEWITHSUNDEEP
mysqlsqlselect
Ambidex
Ambidex

Reputation: 857

MySQL: fetching an id from an associative table, with specific associations

I've got the following associative table between packages and products (simplified):

package_id     product_id    count
1              1             6
1              2             1
1              3             1
2              1             6
2              2             1
3              1             6
4              1             8
4              2             1

I'm trying to work out how to create an query which is able to select specific package_id's which contain exactly the products and their counts I supply. So if I'd be trying to find the package that contains: (product_id = 1 AND count = 6) AND (product_id = 2 AND count = 1), it should only return package_id 2 and not the others, because those contain other products and / or other counts.

I'd be happy to work this out in my code (PHP) instead of SQL, but since I'm trying to get to the bottom of queries, I'd like to know how this is done.

Upvotes: 1

Views: 80

Answers (1)

John Woo
John Woo

Reputation: 263713

This is called Relational Division

SELECT  a.package_ID
FROM    tableName a
WHERE   (a.product_ID = 1 AND a.count = 6) OR
        (a.product_ID = 2 AND a.count = 1)
GROUP   BY a.package_ID
HAVING  COUNT(*) = 2 AND
        COUNT(*) = (SELECT COUNT(*) FROM tableName WHERE package_ID = a.package_ID)

OR

SELECT  package_ID
FROM    tableName
WHERE   (product_ID, `count`) in ((1, 6), (2, 1))
GROUP   BY package_ID
HAVING  COUNT(DISTINCT product_ID, `count`) = 2 AND
        COUNT(*) = (SELECT COUNT(*) FROM tableName WHERE package_ID = a.package_ID)

Upvotes: 1

Related Questions