[thelist] Nested Queries

rudy r937 at interlog.com
Wed Aug 21 12:32:24 CDT 2002


> Let's say I constrain the nesting to four levels deep.
> What would a query then look like?

best viewed with a fixed font --

   ID   name   parentID
    1   tom
    2   dick
    3   harry
    4   ted
    5   moe         4
    6   larry       4
    7   curly       4
    8   A
    9   A.10        8
   10   A.20        8
   11   A.10.a      9
   12   A.10.a.i   11
   13   A.10.a.ii  12
   14   A.20.x     10



   select L1.name as person
        , L2.name as child
        , L3.name as grandchild
        , L4.name as greatgrandchild
     from ( ( treetable L1
   left outer
     join treetable L2
       on L1.ID = L2.parentID )
   left outer
     join treetable L3
       on L2.ID = L3.parentID )
   left outer
     join treetable L4
       on L3.ID = L4.parentID
    where L1.parentID is null

   person   child   grandchild   greatgrandchild
   tom
   dick
   harry
   ted      moe
   ted      larry
   ted      curly
   A        A.10    A.10.a       A.10.a.I
   A        A.20    A.20.x


rudy




More information about the thelist mailing list