3
I have a question about one (or more) select. I have the following bank created and I need to do a search for a certain recipe based on one (or more, and here’s the problem) ingredients.
How can I get the recipe back, obligatorily, all selected ingredients?
OBS: A friend suggested to me Nosql for this kind of structure, do you think it’s more viable? I don’t understand anything about Nosql, but as it is a learning project the option is not discarded.
2
Use the list of tables
SELECT
ingredientes.*,
receitas.*
FROM
ingredientes
INNER JOIN ingredientes_receitas ON (ingredientes.cod_ingrediente = ingredientes_receitas.cod_ingrediente)
INNER JOIN receitas ON (ingredientes_receitas.cod_receita = receitas.cod_receita)
WHERE ingredientes.cod_ingrediente IN ("codigo1","codigo2")
The issue in JOIN, in that case, is that it will return all recipes that have any ingredients that are inside the IN, right? What I was looking for was something that would return only recipes that have both ingredients.
Really Gabriel, this way will bring all the recipes that have at least 1 item, and he wants you to bring only those that have all the items.
2
To display ONLY THE REVENUES who have ALL the ingredients, would be such forms:
Simplified form (I believe, easier):
SELECT re.cod_receita, COUNT(ig.cod_ingrediente)
FROM receitas re
LEFT JOIN ingredientes_receitas ir ON ir.cod_receita = re.cod_receita
LEFT JOIN ingredientes ig ON ig.cod_ingrediente = ir.cod_ingrediente
WHERE ig.cod_ingrediente IN (1,6)
GROUP BY re.cod_receita
HAVING COUNT(ig.cod_ingrediente) = 2
You will pass the ingredients on:
WHERE ig.cod_ingrediente IN (1,6)
And the total amount of ingredients (in this case above are 2) in:
HAVING COUNT(ig.cod_ingrediente) = 2
The HAVING COUNT it is necessary, therefore in this part WHERE ig.cod_ingrediente IN (1,6), you are selecting ANY RECIPE who has at least ONE OF ingredients contained in (1,6).
With sub-querys:
SELECT *
FROM receitas
WHERE cod_receita IN
(
SELECT re.cod_receita
FROM receitas re
LEFT JOIN ingredientes_receitas ir ON ir.cod_receita = re.cod_receita
LEFT JOIN ingredientes ig ON ig.cod_ingrediente = ir.cod_ingrediente
WHERE ig.cod_ingrediente = 1
)
AND cod_receita IN
(
SELECT re.cod_receita
FROM receitas re
LEFT JOIN ingredientes_receitas ir ON ir.cod_receita = re.cod_receita
LEFT JOIN ingredientes ig ON ig.cod_ingrediente = ir.cod_ingrediente
WHERE ig.cod_ingrediente = 6
)
You would have to filter recipes by ingredients, and then see which one is in all selects. For now, you will have to loop by ingredient, and add in the select check of the recipes. In the rush I remembered these forms... later I will see if it has simpler forms, thinking of generating in PHP.
Extra links to JOINS:
Difference between INNER JOIN, JOIN and WHERE?
What is the difference between LIKE, IN and BETWEEN in Mysql?
Gee, it’s the same case I mentioned in Gabriel Carvalho’s answer =/
Sorry, I had not seen Marcos... do a favor, generates a Sqlfiddle that I take a look ! sqlfiddle.com ... if you don’t know, just put the creation script of your bd’s structure, and of Inserts to the example.
Sorry for the delay. Ready, I put in the fiddle, I left some sample data and inserted your query with the explanation. Link: http://sqlfiddle.com/#! 9/32156d/5/0
@Marcostesolin I did running a way that I know works... but when it does, I see if there’s a simpler way for your comic book structure.
Bacana, the first form (with HAVING and COUNT) served my need very well, besides being simple and easy to understand/implement/modify. Thank you =D
0
I ended up getting a solution using Subqueries. I did something like:
select * from receitas where cod_receita in
(select * from ingredientes_receitas ir where cod_ingrediente = "codigo1")
and cod_receita in (select * from ingredientes_receitas ir where cod_ingrediente = "codigo2")
I just don’t know if this is the right solution/ more elegant, because I would have to do several Subqueries as the number of filters increases.
If you have a better solution, hint or correction I am accepting =D
Your choice was the same as my first example, but to generate it is more laborious and requires more processing, for example, if a recipe has 20 ingredients. In my second example, it’s much simpler.
Browser other questions tagged mysql sql select join
You are not signed in. Login or sign up in order to post.
You want the answer in Mysql, or a solution?
– Sveen
Basically you need to make a
joinbetween tables filtering in ingredients_recipes the ingredients and quantity to ensure that all are included– Ricardo Pontual
@Sveen actually I am accepting both. If it is the solution, I just need to understand correctly so that I can implement in My.
– Marcos Tesolin