Intentar devolver solo entradas de ID distintas de una tabla donde todos los valores de una determinada columna deben coincidir

Entonces, la situación es que tengo 3 tablas, 2 de las cuales son tablas de "ayuda", mientras que la otra es la tabla principal de la que estoy tratando de obtener identificaciones distintas:

La tabla principal dbo.recipes tiene columnas ID, nombre y algunas otras como:

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

Los ayudantes son 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

y 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

Básicamente, el dbo.stock es el inventario de ingredientes que tiene el usuario, por lo tanto, la identificación del usuario y la identificación del ingrediente. El dbo.content son los ingredientes necesarios para hacer un determinado plato.

Lo que quiero consultar es SOLO recetas para las que el usuario realmente tiene los ingredientes, lo que significa que todas las recetas que tienen TODOS sus ingredientes coincidentes (para un usuario determinado) deben devolverse. El código que tengo en este momento para mi procedimiento es el siguiente:

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))

que funciona, pero dudo que esta sea la mejor manera de lograrlo. ¿Hay una mejor manera de llegar a lo mismo?

MS SQL Server Express 2019

Answer

Básicamente, desea encontrar todas las recetas donde no hay un ingrediente en el contenido que no esté en stock. No es la forma en que lo piensas en inglés, pero lleva a esto si lo piensas de esa manera en 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
);

Sin embargo, esta consulta es más similar a la suya, solo que sin los costosos DISTINCTs que se encuentran en los dos planes anteriores (así de EXCEPTengañoso ), por lo que probablemente sea la mejor opción:

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
  )
);

Esta es una pregunta clásica de división relacional con resto .

@AaronBertrand le ha brindado un par de buenas soluciones. Aquí hay otro que se usa a menudo.

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);

Esto unirá todo (uniendo a la izquierda el stock), agrupará por recipey devolverá solo aquellas agrupaciones que tengan el mismo número de filas que filas no nulas stock. En otras palabras, todos contentdeben coincidir y debe haber al menos uno content.

Hay una diferencia semántica: si quisiera también todo recipeslo que no tiene `contenido, puede cambiarlo ligeramente.

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);

db<>violín