What am I / Was I missing on this SQL request?

user_16699537 0
Viewed 44

I apologize for any vagueness, this is about a question I had on a entry level SQL position test last week. I couldn't figure out how to do this at the time, and can't seem to figure anything out now.

Basically I was provided with 3 tables. One was Recipes (had recipe name, ID, instructions and notes), one was RecipeIngredients (had recipe ID, ingredient ID, ingredients, and quantity of ingredients), and the third was Ingredients (ingredient ID and ingredient). Something like that.

I had a few queries with JOIN statements that showed how to make certain recipes and so on. But I couldn't figure out quite how to manage the final question. The final question was something like:

"Provide 2 sets of queries at once. 1st query - Return Ingredients, quantities, and notes for a specific ID. 2nd Query - Return the instructions for the same Recipe ID. Write the queries so that the user can easily alter the recipe ID in one place only for both queries in order to query for different recipe IDs."

I know we can't alias a WHERE clause, but that is the only thing I can remotely think of for doing 2 queries at once with only specifying the WHERE once. I tried to see if I could do it with a subquery but had no luck. I considered UNION... but there were different columns and different values in each query so that's a no go.

Is there something I'm missing? Or did I just completely fail when trying to set this up as a subquery? I apologize for vagueness, it's been a few days and I've been too busy to remember to post this on here. I've found a lot of help anonymously browsing this site in the past so I figured it was worth posting this as I've not seen anything similar so far.

sql
1 Answers

Using SQL Server, you could have declared a variable, like @recipeID, then used it in two queries. That would allow you to change the value in one place, and have it used in 2 queries.

DECLARE @recipeID INT = 123

SELECT * 
FROM recipes 
WHERE recipeID = @recipeID

SELECT * 
FROM recipe_ingredients 
WHERE recipeID = @recipeID
user_54647470
Related