开发者

SQL Conditional Select Statement

开发者 https://www.devze.com 2022-12-21 13:06 出处:网络
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.

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消