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