SQL select all dishes containing a list of ingredients-CodePudding

I am looking to build a query which would return the dishes that contain a list of ingredients but I can't figure it out. The dish returned must contain at least the list of ingredients requested.

Ingredients
============
ID
IngredientName
IngredientAmount
P_ID


Dishes
==========
ID
DishName


DishIngredients
==========
ID
DishID
IngredientID

So far I buit following query but it returns all meals that contain at least 1 of the ingredients in the list but not only those which contain all ingredients in the list.

Remarks : The list of ingredients to match is a string converted to a table because I want to use it as an argument in a stored procedure.

DECLARE @IdIngredients nvarchar(1024) = '4174, 4028'
DECLARE @Ingredients TABLE (IdIngredient int) 
INSERT @Ingredients (IdIngredient)
SELECT Convert(int, value) FROM STRING_SPLIT(@IdIngredients, ',') 

SELECT DISTINCT D.Id
FROM Dishes D
INNER JOIN DishIngredients DI ON DI.DishID = D.ID
WHERE IngredientID IN (SELECT IdIngredient FROM @Ingredients)

Any help would be welcome, I am sure there is a way to manage it either with a jointure or by counting matching ingredients but I can't figure it out

While writing this post, I maybe found a solution but I still need to test it

DECLARE @IdIngredients nvarchar(1024) = '4174, 4028'
DECLARE @Ingredients TABLE (IdIngredient int) 
INSERT @Ingredients (IdIngredient)
SELECT Convert(int, value) FROM STRING_SPLIT(@IdIngredients, ',') 

SELECT DISTINCT D.Id
FROM Dishes D
INNER JOIN DishIngredients DI ON DI.DishID = D.ID
WHERE IngredientID IN (SELECT IdIngredient FROM @Ingredients)
GROUP BY D.Id
HAVING COUNT(D.Id) = (SELECT COUNT(*) FROM @Ingredients)

CodePudding user response：

A double NOT EXISTS should do the trick:

SELECT D.*
FROM Dishes As D
WHERE Not Exists
(
    SELECT 1
    FROM @Ingredients As I
    WHERE Not Exists
    (
        SELECT 1
        FROM DishIngredients As DI
        WHERE DI.DishID = D.Id
        And DI.IngedientId = I.IdIngredient
    )
)

Or, if you'd prefer, a NOT EXISTS with an EXCEPT:

SELECT D.*
FROM Dishes As D
WHERE Not Exists
(
    SELECT IdIngredient
    FROM @Ingredients
    
    EXCEPT
    
    SELECT IngredientId
    FROM DishIngredients As DI
    WHERE DI.DishID = D.Id
)

EXCEPT and INTERSECT (Transact-SQL) - SQL Server | Microsoft Docs

CodePudding user response：

So far I buit following query but it returns all meals that contain at least 1 of the ingredients in the list but not only those which contain all ingredients in the list.

When you split the ingredients list, you can gather how many ingredients there are. Let it be X.

Now if you join the dishes with the recipes,

SELECT D.*, COUNT(*) AS N
FROM Dishes As D
JOIN DishIngredients AS DI ON (DI.DishID = D.ID)
JOIN Ingredients AS I ON (I.ID = DI.IngredientID)
WHERE Ingredients.IngredientName IN (@IngredientNameList)
GROUP BY D.ID

it will report, for each dish, how many ingredients there are from the supplied ingredient list.

The dishes you want are those HAVING N = X.

(If you have the ingredients IDs, even better, you can save yourself the JOIN with Ingredients and use IngredientID instead).