I have a question about one (or more) select . I have the following bank set up and I need to do a search for a particular recipe based on one (or more, and oh, that's the problem) ingredients.
How do I get the recipe that contains all the ingredients selected to return the recipe?
OBS : A friend suggested me NoSQL for this type of structure, do you find it more feasible? I do not understand anything about NoSQL , but as it is a learning project the option is not ruled out.
To display ONLY REVENUES that have ALL ingredients, it would be as follows:
Simplified form (I think, 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
HAVING COUNT is required because in this part WHERE ig.cod_ingrediente IN (1,6) , you are selecting ANY RECIPE that has at least ONE 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 out recipes by ingredients, and then see which one is in all selects. For the time being, you will have to loop by ingredient, and add in the recipes select check. 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?
Use the table relation
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")
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 do not know if this is the right / most elegant solution, because I would have to do several subqueries as the amount of filters increases.
If you have a better solution, hint or fix I am accepting = D