How can I make a query that return only the exact list of parameters

I have a table that has the ingredient information and I would like to return only if the value is exactly as the parameter I receive.

The Table structure is:

Name           | Recipe
Ingredient X   | Recipe1
Ingredient Y   | Recipe1
Ingredient Z   | Recipe2

and if the user inform the ingredient X, the query should not return, but if the user inform X and Y, the query should return Recipe1. If Ingredient X and Z are informed, it should not return too.

can anyone help with this query?

Answer

You can use conditional aggregation:

select recipe
from t
group by recipe
having sum(case when name in (<name list>) then 1 else 0 end) = count(*) and
       sum(case when name not in (<name list>) then 1 else 0 end) = 0;

The conditions test that all the ingredients are in the recipe and that no other ingredients are.

Advertisement

Answer