CODEWITHSUNDEEP
mysqlselectgroup-byrelational-division
user1417418
user1417418

Reputation:

MySQL get all related IDs from a table matching an array

I'm facing a problem while trying to retrieve all productIDs from a table if they match all items in an array, in this case, return products only if they contain every ingredient the user searched for.

Table looks like this

    ID  produktID   ingredientID
    ----------------------------
    1      418           1
    2      418           2
    3      418           3
    4      416           4
    5      411           1
    6      411           5
    7      411           6

I join this table from a products table where the main information is stored. The aim of the query should be to retreive a productID only when all ingredientIDs match with the given array. I've tried using WHERE ingredientID IN(1,5,6) but it always turns out to be an OR statement, returning every ID where any of the ingredients are matched. So for example, if I pass (1,5,6) or (5,6) the product ID 411 should be returned, but if I pass (2,5,6) it should not.

The query I tried looks like this (simplified, it's part of a 5 way join to other relations like brands and catgories)

SELECT productID FROM products_ingredients_mm WHERE ingredientID IN (1,5,6) GROUP BY productID

but the result contains 418 aswell. How do I get it to match? I hope I was able to describe the problem in an understandable way, it's really hard for me to wrap my head around it to ask a question.

Upvotes: 2

Views: 2060

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT pi.productID, p.productName
FROM products_ingredients_mm pim 
INNER JOIN products p ON pim.productID = p.productID 
WHERE ingredientID IN (1,5,6)
GROUP BY productID
HAVING COUNT(DISTINCT ingredientID) = 3

Upvotes: 2

John Woo
John Woo

Reputation: 263733

This is called Relational Division.

SELECT  produktID
FROM    tableName
WHERE   ingredientID IN (1,5,6)
GROUP   BY produktID
HAVING  COUNT(*) = 3

If a unique constraint was not enforce on ingredientID for every produktID, then you need to use DISTINCT

SELECT  produktID
FROM    tableName
WHERE   ingredientID IN (1,5,6)
GROUP   BY produktID
HAVING  COUNT(DISTINCT ingredientID) = 3

Other Source

Upvotes: 3

Related Questions