Parent Child Query

I have a table and some same data.
entity_id | parent_entity_id | entity_name
-- -- --
4 0 Address Control
5 4 Address Line 1
6 4 Address Line 2
7 4 City
8 4 State
9 4 Zip Code
10 8 Maine
11 8 Massachusetts
12 8 New Hampshire
13 8 Rhode Island
14 8 Vermont
15 8 Connecticut
I'd like to pass in the entity_id, in this case 4 and get back all of the
children and grandchildren and I possibly down N, but would be happy with
grandchildren at this point as I dont know if deeper is needed.
If someone could point me to an article maybe or old post. I have looked
around some but see a lot of Oracle stuff.
Thanks"Brian" <brian@.nospam.com> wrote in message
news:uzKYuJqnFHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a table and some same data.
> entity_id | parent_entity_id | entity_name
> -- -- --
> 4 0 Address Control
> 5 4 Address Line 1
> 6 4 Address Line 2
> 7 4 City
> 8 4 State
> 9 4 Zip Code
> 10 8 Maine
> 11 8 Massachusetts
> 12 8 New Hampshire
> 13 8 Rhode Island
> 14 8 Vermont
> 15 8 Connecticut
> I'd like to pass in the entity_id, in this case 4 and get back all of the
> children and grandchildren and I possibly down N, but would be happy with
> grandchildren at this point as I dont know if deeper is needed.
> If someone could point me to an article maybe or old post. I have looked
> around some but see a lot of Oracle stuff.
> Thanks
>
Get Celko's books on Trees and Hierarchies. Other items of interest, you
can google for nested set theory.
Rick Sawtell|||The following example includes a function that can be used to find
descendants and ascendants of an item in a hierarchy.
http://milambda.blogspot.com/2005/0...or-monkeys.html
A vital element is missing from your model - prevention of circular
references.
ML