subquery/nested query using same table

[Accessrocks]

I am actually trying to write sql statement to generate report in ALM (quality center). It does not have capability to create multiple queries then join them together in different query (not that i know of) so I would need to do all this in one query.



This is what I would do if I was doing it in Access.

From table below, first I need to get ID for Parent:

Select * from FOOD Where FATHER_ID = 0 save query as qryMain

Then create another query:

Select FOOD.ID, FOOD.Name, qryMain.NAME
FROM qryMain inner join FOOD ON qrymain.ID = FOOD.ID

Table name: FOOD

Name	ID	FATHER_ID
Breakfast	608	0
Lunch	609	0
Dinner	610	0
Eggs	10	608
bacon	11	608
meatloaf	22	610
mashed potato	23	610
turkey sandwich	35	609

The result would something like this:

Name	Name2	ID
Breakfast	Egg	10
Breakfast	Bacon	11
Lunch	turkey sandwich	35
Dinner	mashed potato	22
Dinner	meatloaf	23

I know there is a way to do all this using sub/nested query but I just know basic sql so not sure where to start.

Thank you in advance for your help

[June7]

I don't understand why you need qryMain. All it does is select all fields and apply filter. This can be done in a query that joins the two tables directly.

[Accessrocks]

Reason why I create qrymain first is because I am sql beginner and do not know how to create sub/nested queries. All the data I need is in one table.. there is only 1 table. and Correction I apologize, sql is incorrect. It should be something like this:

Select FOOD.ID, FOOD.Name, qryMain.NAME
FROM qryMain inner join FOOD ON qrymain.ID = FOOD.FATHER_ID

[June7]

Okay, I see that now, same table. And the revised query does help clarify. I was just about to ask if that's what you actually intended.


Select FOOD.ID, FOOD.Name, qryMain.NAME FROM

(Select * from FOOD Where FATHER_ID=0) AS qryMain

Inner join FOOD ON qryMain.ID = Food.Father_ID;