Message-ID: <F10BB1FAF801D111829B0060971D839F6D762A@cpsmail>
From: "Jackson, DeJuan" <djackson@cpsgroup.com>
To: PGSQL SQL <pgsql-sql@postgresql.org>
Subject: FW: Trees in SQL
Date: Wed, 3 Mar 1999 15:49:02 -0600
MIME-Version: 1.0
Content-Type: text/plain


-----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