ScottPletcher (10/7/2015)
For efficiency, try this. It is also easy to extend this to include/exclude multiple ingredients.
SELECT ra.Recipe_number, r.Recipe_name
FROM (
SELECT Recipe_number
FROM tblRecipeAllergen
GROUP BY Recipe_number
HAVING MAX(CASE WHEN Allergen = 'Milk' THEN 1 ELSE 0 END) = 0
) AS ra
INNER JOIN tblRecipe r ON r.Recipe_number = ra.Recipe_number
Efficiency? Why would a subquery with a aggregate function be more efficient?
Extending any of these to allow for additional ingredients is also pretty easy.
As a simple test, I created these tables with a millions rows and ran the same queries 10 times.
Your version averaged 3328 milliseconds, the not exists version averaged 2117 milliseconds.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/