I'm new so please excuse me if this is a dumb question but i can't figure it out. Question: Write a query that lists the name of Products that appear in more than one Recipe. These are the tables

CodePudding user response：

The simplest solution to this question is to use a GROUP BY expression with a HAVING clause to limit the results to groups that have a count of more than 1

SELECT Nome_produto
FROM Receita
LEFT JOIN Ingrediente ON Receita.Codigo_receita = Ingrediente.Codigo_receita
LEFT JOIN Produto ON Ingrediente.Codigo_produto = Produto.Codigo_produto 
GROUP BY Nome_produto
HAVING COUNT(Receita.Codigo_receita) > 1

The key to solving an issue like this is to first build the relationship between the entities that the requirement refers to, in this case I'm selecting data FROM the recipe table first, as this is the subject that the principal predicate must be applied to:

...that appear in more than one Recipe.

Once you have a denormalized or flat table of results, we can apply the grouping with a HAVING clause. HAVING is similar to WHERE except that it is evaluated after the grouping and allows us to use the results from aggregate expressions in conditional statements.

WHERE is applied to individual rows and will exclude rows before the grouping sets are realized and therefore before aggregates are evalutated.

• Read more about SQL HAVING Clause here @ W3 Schools.