[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