SQL Conditional Select Statement

Question

Hello all, 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.

Answer 1

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.

Answer 2

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.

Answer 3

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.