Cookbook SQL

I have a DB with 3 tables:
dishes (dishID, dishName, calories, etc)
ingredients (ingredID, ingredName, cost, etc)
recipes (recipeID, dishIDFK, ingredIDFK)

What I want is:
1. Spaghetti
a)Noodles
b)Sauce
2. Tuna Surprise
a)Noodles
b)Peas
c)Tuna
.
.
.
N. Dish N
a)Ingredient N

What's the SQL that gives me that?

What I am currently getting with the code I have is:
1. Spaghetti
a)Noodles
2. Spaghetti
b)Sauce
3. Tuna Surprise
a)Noodles
4. Tuna Surprise
b)Peas
5. Tuna Surprise
c)Tuna

Try providing a sample of your database data so we can determine what data is in what fields.

(contents of db)
Table 1: dishes
field 1: dishID (1, 2, 3...)
field 2: dishName (spaghetti, tuna surprise, chicken kiev...)

Table 2: ingredients
field 1: ingredientID (1, 2, 3,...)
field 2: ingredientName (noodles, tomato sauce, tuna, chicken, flour...)

Table 3: recipes
field 1: recipeID (1, 2, 3,...)
field 2: dishIDFK (corresponding to above)
field 3: ingredientIDFK (corresponding to appropriate dish)

Let me try being more clear.
Post some sample data, means post what is actually in the fields in your database.

Let me try to be more clear:
Here are the contents of table 1, "dishes":
dishesID...dishName
1...spahetti
2...tuna surprise

Here are the contents of table 2, "ingredients":
ingredID...ingredName
1...noodles
2...tomato sauce
3...peas
4...tuna

Here are the contents of table 3, "recipes":
recipeID...dishIDFK...ingredIDFK
1...1...1
2...1...2
3...2...1
3...2...3
3...2...4

Do the dots help? Or do you actually have to see it in cells?

-----------------------
dishID | dishName |
-----------------------
1 | spaghetti |
-----------------------
2 | tuna surprise|
-----------------------

------------------------
ingredID | ingredName |
------------------------
1 | noodles |
------------------------
2 | tomato sauce|
------------------------
3 | peas |
------------------------
4 | tuna |
------------------------

----------------------------------
recipeID | dishIDFK | ingredIDFK |
----------------------------------
1 | 1 | 1 |
----------------------------------
2 | 1 | 2 |
----------------------------------
3 | 2 | 1 |
----------------------------------
4 | 2 | 3 |
----------------------------------
5 | 2 | 4 |
----------------------------------

You won't be able to return it in a Parent/Child format.
But here is the query.

You are going to have to use code to build what you are after,

Or put Menoch SQL into a report where you hide duplicate(repeating) fields
thus giving you something like

spahetti Noodles
tomato sauce
tuna surprise peas
tuna

OR use groupings in your report


The problem is SQL can't give you what you want, you have to take what SQL gives and make it work in a report.

S-


Menoch, Get an extra hour of sleep today? You sure are patient today.

I'm getting too tired to lose my patience with people.

But tomorrow is another day and I will be back to my old "Impatient" self!