Hierarchical Query! Stoping at a particular value.

From: Prince Kumar <gspk_at_yahoo.com>
Date: 28 Aug 2002 13:02:16 -0700
Message-ID: <629275ba.0208281202.5dc97ed2@posting.google.com>

I want to get the list of all values starting from the child to the parent until a certain condition is met.

How do I write a efficient SQL for this?

Ex.

---

select * from my t ;

Key PKey Crit
---- ----- -----
1 null null

4 3 T

7 4 F

8 7 T

11 8 null     

17 11 null

16 null null

19 14 null

I want to start at a particular "Key" and find all its parent till the first "Crit" = 'T'. if "Crit" is null keep on finding the parent until the root level is met.

Ex,

if I start with Key = 9, I should get

---

19 14 null --> no more parents

if I start with Key = 17, I should get

---

17 11 null

11     8    null
8      7    T  --> Crit "T" is met (do not go further)

if I start with Key = 4, I should get

---

4 3 T --> Crit "T" is met

Thanks, Received on Wed Aug 28 2002 - 15:02:16 CDT