Reading from One Table

[jerrymedina]

I have a table that I'm trying to create a query for.
example:

Table Agencies:


ID Agency_ID Agency name
--- ----------- ------------
1 <null> Parent Agency
2 1 Child_Agency_Level2
3 2 Child_Agency_Level3
4 2 Child_Agecny_Level3a
5 <null> Parent_Agency_2
6 5 Child_Agency_Level2a

I'm trying to create a query where I can return the name of the Child Agency &quot;Child_Agency_Level2&quot; (Child of ID 1)
and then
write another query where I can retrieve just the &quot;Child_Agency_Level3&quot; (Child of ID 2) based on the ID field.
If I type in &quot;Child_Agency_Level2&quot; I want to return all the Level 3's.
And if I type in Parent Agency I want to return all the agecnies whom are level2's

Thanks ahead of time.

Jerry

 

[Mdavis123]

Jerry,
Maybe it is my suffering from a cold but your not making much sense here. Lets do the first query.
You have
ID Agency_ID Agency name

Select ID, Agency_ID, [Agency name]
from Agency
Where ???? <==== What criteria do you want to use
where does Child of ID 1 mean

MikeD

 

[SleepingSand]

Are you trying to get the result for this Self Related Table in the form of a Tree Structure ?