Trying to only return distinct ID entries from a table where all values of a certain column must be-CodePudding

So the Situation is that I have 3 Tables, 2 of which are "helper" tables while the other one is the main table I'm trying to get distinct IDs out of:

Main Table dbo.recipes has columns ID, Name and some others such as:

ID  NAME
5   Veggie Cassola
6   Mozzarella Penne
7   Wiener Schnitzel with Fries
8   Grilled Salmon with Rice
9   Greek style Salad

The helpers are dbo.stock:

ID_USER     ID_INGREDIENT
1           225
1           585
1           607
1           643
1           763
1           874
1           937
1           959
1           960
2           225
2           246
2           331
2           363
2           511
2           585

and dbo.content:

ID_INGREDIENT   ID_RECIPE
98              5
196             5
333             5
607             5
608             5
613             5
627             5
643             5
763             5
874             5
951             5
956             5
225             6
585             6
607             6

Basically the dbo.stock is the inventory of ingredients that the user has hence user id and ingredient id. The dbo.content is the ingredients needed to make a certain dish.

What I want to query is ONLY recipes that the user actually has the ingredients for, so that means that all the recipes which have ALL their ingredients matched (to a certain user) should be returned. The code that I have at the moment for my procedure is as follows:

SELECT * FROM [dbo].[recipe]
    WHERE [recipe].[id] NOT IN
    (SELECT DISTINCT [content].[id_recipe] FROM [dbo].[content]
        WHERE [content].[id_ingredient] NOT IN
            (SELECT [stock].[id_ingredient] FROM [dbo].[stock]
                WHERE [stock].[id_user] = @userID))

which works, but I doubt this is the best way to achieve this. Is there a better way to reach the same?

MS SQL Server Express 2019

CodePudding user response：

Basically, you want to find all the recipes where there isn't an ingredient in content that is not in stock. It's not the way you think about it in English, but it leads to this if you think about it that way in SQL:

DECLARE @userID int = 1;

SELECT ID, NAME
FROM dbo.recipe AS r
WHERE NOT EXISTS
( 
  SELECT id_ingredient FROM dbo.content WHERE id_recipe = r.ID
  EXCEPT
  SELECT id_ingredient FROM dbo.stock WHERE id_user = @userID
);

However, this query is more along the lines of yours, just without the expensive DISTINCTs found in both of the above plans (EXCEPT is sneaky like that), so is probably the best option:

DECLARE @userID int = 1;

SELECT ID, NAME
FROM dbo.recipe AS r
WHERE NOT EXISTS
( 
  SELECT 1 FROM dbo.content AS c
  WHERE id_recipe = r.ID AND NOT EXISTS 
  (
    SELECT 1 FROM dbo.stock 
    WHERE id_ingredient = c.id_ingredient
      AND id_user = @userID
  )
);

Example db<>fiddle