FW: Trees in SQL

-----Original Message-----
From: Jackson, DeJuan
Sent: Wednesday, March 03, 1999 1:18 PM
To: 'Bartek Teodorczyk'
Subject: RE: Trees in SQL

> I'm trying to implement set model of trees in SQL and I found
> a problem:
>
> How to find first level children of parent?
>
> For this structure:
>
> Jerry
> |
> -------------------
> | |
> Bert Chuck
> |
> ------------
> | |
> Donna Fred
>
> The question is: Who is first level child of Jerry?
> The answer should produce:
> Bert
> Chuck
>
> Maybe you know how to formulate the query?
>
> BarTeo

Assuming the above tree structure we have:

l | r | data
------------
1 |10 | Jerry
2 | 3 | Bert
4 | 9 | Chuck
5 | 6 | Donna
7 | 8 | Fred

What we need to do is find all children of Jerry that isn't a child of
anyone else who is a child of Jerry.
SELECT p1.*
FROM people p1, people p2
WHERE p2.l<p1.r AND p2.r>p1.r AND
p2.data = 'Jerry' AND
NOT EXISTS(SELECT 1 FROM people p3
WHERE p3.l<p1.r AND p3.r>p1.r AND
p2.l<p3.l AND p2.r>p3.r)

Results:
l|r|data
-+-+-----
2|3|Bert
4|9|Chuck

Hope this helps,
-DEJ