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
Message-ID: <F10BB1FAF801D111829B0060971D839F6D762A@cpsmail> (raw)
-----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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: djackson@cpsgroup.com
Subject: Re: FW: Trees in SQL
In-Reply-To: <F10BB1FAF801D111829B0060971D839F6D762A@cpsmail>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox