Query in Mysql to get records that have the same records in another table

Question

Query in Mysql to get records that have the same records in another table

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I have the following three tables:

With the following information: Recipe:

Ingredient:

RECIPE_INGREDIENTE:

Now for example I want to bring the recipes that have the ingredients leche(6) y Azúcar(7) The result that I hope is:

Pay de limón
Chocolate

Try the following without success:

select * from RECETA_INGREDIENTE RI
INNER JOIN RECETA R 
ON R.ID_RECETA = RI.ID_RECETA
INNER JOIN INGREDIENTE I 
ON I.ID_INGREDIENTE = RI.ID_INGREDIENTE 
where RI.ID_INGREDIENTE = 6 AND RI.ID_INGREDIENTE = 7;

mysql query

asked by Isra 23.11.2018 в 02:00

source

2 answers

0

This is the only way I can come up with a bit forced:

SELECT DISTINCT(receta.nombre)
from receta
join receta_ingrediente
on receta.id_receta = receta_ingrediente.id_receta
WHERE 
(receta_ingrediente.id_ingrediente IN (6,7))
AND receta.nombre <> "sopa";

I use distinct because more than one recipe uses any of the listed ingredients, so we avoid showing it repeated

I use the IN operator to tell you to look for matches between those two parameters

at the end I indicate that from the recipe table and from the column name discard with <> the soup name that finally in the final result turns out to be the id 5 which we need to remove from the final result

Here is the fiddle where I developed the exercise link

answered by 23.11.2018 в 03:36

Find an Image name with File Exists in C # Use files from another virtual host

score 0 · Accepted Answer

This query basically selects all the ingredients you need in the WHERE and the COUNT DISTINCT ensures that they are the 2 different ones, and is grouped by recipe.

SELECT r.NOMBRE, i.NOMBRE FROM
RECETA r
JOIN RECETA_INGREDIENTE ri ON ri.ID_RECETA = r.ID_RECETA
JOIN INGREDIENTE i ON i.ID_INGREDIENTE = ri.ID_INGREDIENTE
WHERE i.ID_INGREDIENTE IN(6,7)
GROUP BY r.ID_RECETA
HAVING COUNT(DISTINCT i.ID_INGREDIENTE ) = 2

Test in sqlfiddle