How would I go about doing the following?
Given 开发者_运维技巧the tables, Recipe, RecipeItem, RecipeInstruction. How do I perform a SELECT only if the SELECT statement of Recipe returned results.
If Recipe exists, return RecipeItems and return RecipeInstructions.
Not sure if this is what you're looking for, but assuming a key relationship, select statements of the form:
SELECT ri.* FROM Recipe r
JOIN RecipeItem ri ON ri.RecipeID = r.RecipeID
WHERE r.Name = @myName -- or other criteria
SELECT ris.* FROM Recipe r
JOIN RecipeInstructions ris ON ris.RecipeID = r.RecipeID
WHERE r.Name = @myName -- or other criteria
... will return recipe details only if that recipe ID exists. This is the standard way to retrieve child items with SQL.
You want an INNER JOIN:
SELECT *
FROM Recipe
INNER JOIN RecipeItem ON RecipeItem.RecipeID = Recipe.RecipeID
INNER JOIN RecipeInstruction ON RecipeInstruction.RecipeID = Recipe.RecipeID
WHERE Recipe.[Name] = 'the recipe name'
This is assuming that both RecipeItem
and RecipeInstruction
have a foriegn key called RecipeID
that links it back to the main Recipe
table.
Here's a start, but your question is not clear:
Select RI.*
, RInst.*
From Recipe AS R
inner join RecipeItem AS RI
on R.PK = RI.FK
inner join RecipeInstruction AS RInst
on R.PK = RInst.FK
The primary key (PK) needs to match the Foreign Key (FK) fields between these tables in some way. I'm going to suggest listing some fields.
精彩评论