sql - una - tabla intermedia uno a muchos

Consulta SQL a través de una tabla intermedia.

Question

Dadas las siguientes tablas:

Recipes
| id | name
| 1  | ''chocolate cream pie''
| 2  | ''banana cream pie''
| 3  | ''chocolate banana surprise''

Ingredients
| id | name
| 1  | ''banana''
| 2  | ''cream''
| 3  | ''chocolate''

RecipeIngredients
| recipe_id | ingredient_id
|     1     |      2
|     1     |      3
|     2     |      1
|     2     |      2
|     3     |      1
|     3     |      3

¿Cómo construyo una consulta SQL para encontrar recetas donde ingredients.name = ''chocolate'' and ingredients.name = ''cream''?

Answer 1

Esto se llama división relacional. Una variedad de técnicas se discuten here .

Una alternativa aún no dada es el doble NO EXISTE.

SELECT r.id, r.name
FROM Recipes r
WHERE NOT EXISTS (SELECT * FROM Ingredients i
                  WHERE name IN (''chocolate'', ''cream'')
                  AND NOT EXISTS
                      (SELECT * FROM RecipeIngredients ri
                       WHERE ri.recipe_id = r.id
                       AND ri.ingredient_id = i.id))

Answer 2

Si está buscando varias asociaciones, la forma más sencilla de escribir la consulta es utilizar varias condiciones EXISTS lugar de una ÚNICA JOIN .

SELECT r.id, r.name
FROM Recipes r
WHERE EXISTS
(
    SELECT 1
    FROM RecipeIngredients ri
    INNER JOIN Ingredients i
        ON i.id = ri.ingredient_id
    WHERE ri.recipe_id = r.id
    AND i.name = ''chocolate''
)
AND EXISTS
(
    SELECT 1
    FROM RecipeIngredients ri
    INNER JOIN Ingredients i
        ON i.id = ri.ingredient_id
    WHERE ri.recipe_id = r.id
    AND i.name = ''cream''
)

Si está seguro de que las asociaciones son únicas (es decir, una sola receta puede tener solo una instancia de cada ingrediente), puede hacer trampa un poco utilizando una subconsulta de agrupación con una función COUNT y posiblemente acelerarla (el rendimiento dependerá de el DBMS):

SELECT r.id, r.Name
FROM Recipes r
INNER JOIN RecipeIngredients ri
    ON ri.recipe_id = r.id
INNER JOIN Ingredients i
    ON i.id = ri.ingredient_id
WHERE i.name IN (''chocolate'', ''cream'')
GROUP BY r.id, r.Name
HAVING COUNT(*) = 2

O, si una receta puede tener múltiples instancias del mismo ingrediente (sin restricción UNIQUE en la tabla de asociación de RecipeIngredients ), puede reemplazar la última línea con:

HAVING COUNT(DISTINCT i.name) = 2

Answer 3

Utilizar:

  SELECT r.name
    FROM RECIPES r
    JOIN RECIPEINGREDIENTS ri ON ri.recipe_id = r.id
    JOIN INGREDIENTS i ON i.id = ri.ingredient_id
                      AND i.name IN (''chocolate'', ''cream'')
GROUP BY r.name
  HAVING COUNT(DISTINCT i.name) = 2

El punto clave aquí es que el conteo debe ser igual al número de nombres de ingredientes. Si no se trata de un recuento distinto, existe el riesgo de falsos positivos debido a duplicados.

Answer 4

una forma diferente:

Versión 2 (según procedimiento almacenado) revisada

select   r.name
from   recipes r
where   r.id  = (select  t1.recipe_id
        from  RecipeIngredients t1 inner join
     RecipeIngredients     t2 on t1.recipe_id = t2.recipe_id
     and     t1.ingredient_id = @recipeId1
     and     t2.ingredient_id = @recipeId2)

Edición 2: [antes de que la gente empiece a gritar] :)

Esto se puede colocar en la parte superior de la versión 2, lo que permitirá realizar consultas por nombre en lugar de pasar el ID.

select @recipeId1 = recipe_id from Ingredients where name = @Ingredient1
select @recipeId2 = recipe_id from Ingredients where name = @Ingredient2

He probado la versión 2, y funciona. La mayoría de los usuarios en los que se vinculaban en la tabla de ingredientes, en este caso no era totalmente necesario.

Edición 3: (resultados de la prueba);

Cuando se ejecuta este procedimiento almacenado estos son los resultados.

Los resultados son del formato (Primera Receta_id; Segunda Receta_id, Resultado)

1,1, Failed
1,2, ''banana cream pie''
1,3, ''chocolate banana surprise''
2,1, ''banana cream pie''
2,2, Failed
2,3, ''chocolate cream pie''
3,1, ''chocolate banana surprise''
3,2, ''chocolate cream pie''
3,3, Failed

Claramente, esta consulta no maneja el caso cuando ambas restricciones son iguales, pero funciona para todos los demás casos.

Edición 4: (manejando el mismo caso de restricción):

reemplazando esta linea:

r.id = (select t1...

a

r.id in (select t1...

Trabaja con los casos fallidos para dar:

1,1, ''banana cream pie'' and ''chocolate banana surprise''
2,2, ''chocolate cream pie'' and ''banana cream pie''
3,3, ''chocolate cream pie'' and ''chocolate banana surprise''

Answer 5

SELECT DISTINCT r.id, r.name
FROM Recipes r
INNER JOIN RecipeIngredients ri ON
    ri.recipe_id = r.id
INNER JOIN Ingredients i ON
    i.id = ri.ingredient_id
WHERE
    i.name IN ( ''cream'', ''chocolate'' )

Editado siguiente comentario, gracias! Esta es la manera correcta entonces:

SELECT DISTINCT r.id, r.name
FROM Recipes r
INNER JOIN RecipeIngredients ri ON
    ri.recipe_id = r.id
INNER JOIN Ingredients i ON
    i.id = ri.ingredient_id AND
    i.name = ''cream''
INNER JOIN Ingredients i2 ON
    i2.id = ri.ingredient_id AND
    i2.name = ''chocolate''

Answer 6

select r.*
from Recipes r
inner join (
    select ri.recipe_id
    from RecipeIngredients ri 
    inner join Ingredients i on ri.ingredient_id = i.id
    where i.name in (''chocolate'', ''cream'')
    group by ri.recipe_id
    having count(distinct ri.ingredient_id) = 2
) rm on r.id = rm.recipe_id