SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.recipe(ID int,  NAME nvarchar(128));
CREATE TABLE dbo.stock(ID_USER int, ID_INGREDIENT int);
CREATE TABLE dbo.content(ID_INGREDIENT int, ID_RECIPE int);

INSERT dbo.recipe(ID, NAME) VALUES
(5,   'Veggie Cassola'),
(6,   'Mozzarella Penne'),
(7,   'Wiener Schnitzel with Fries'),
(8,   'Grilled Salmon with Rice'),
(9,   'Greek style Salad');

INSERT dbo.stock(ID_USER, ID_INGREDIENT) VALUES
(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);

INSERT dbo.content(ID_INGREDIENT, ID_RECIPE) VALUES
(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);
35 rows affected

DECLARE @userID int = 1;

SELECT
  r.Id,
  r.Name
FROM dbo.recipe AS r
JOIN dbo.content AS c ON c.id_recipe = r.ID
LEFT JOIN dbo.stock AS s ON s.id_ingredient = c.id_ingredient
    AND s.id_user = @userID
GROUP BY
  r.Id,
  r.Name
HAVING COUNT(*) = COUNT(s.id_ingredient);


Id
Name


6
Mozzarella Penne


Warning: Null value is eliminated by an aggregate or other SET operation.


DECLARE @userID int = 1;

SELECT
  r.Id,
  r.Name
FROM dbo.recipe AS r
LEFT JOIN dbo.content AS c ON c.id_recipe = r.ID
LEFT JOIN dbo.stock AS s ON s.id_ingredient = c.id_ingredient
    AND s.id_user = @userID
GROUP BY
  r.Id,
  r.Name
HAVING COUNT(c.id_recipe) = COUNT(s.id_ingredient);


Id
Name


9
Greek style Salad

8
Grilled Salmon with Rice

6
Mozzarella Penne

7
Wiener Schnitzel with Fries


Warning: Null value is eliminated by an aggregate or other SET operation.

Id	Name
9	Greek style Salad
8	Grilled Salmon with Rice
6	Mozzarella Penne
7	Wiener Schnitzel with Fries