SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL by John Viescas & Michael J. Hernandez

Author:John Viescas & Michael J. Hernandez
Language: eng
Format: epub
Publisher: Addison-Wesley Professional
Published: 2014-08-23T04:00:00+00:00

* * *

Note

You guessed it! This is another example where you must build the filtered INNER JOIN result set first and then OUTER JOIN that with the table from which you want “all” rows.

* * *

Recipes Database

“List ingredients not used in any recipe yet.”

Translation/Clean Up

Select ingredient name from the ingredients table left outer joined with the recipe ingredients table on ingredients.ingredient ID in the ingredients table matches = recipe_ingredients.ingredient ID in the recipe ingredients table where recipe ID is null

SQL

SELECT Ingredients.IngredientName

FROM Ingredients

LEFT OUTER JOIN Recipe_Ingredients

ON Ingredients.IngredientID =

Recipe_Ingredients.IngredientID

WHERE Recipe_Ingredients.RecipeID IS NULL

CH09_Ingredients_Not_Used (20 rows)

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.