Received: from Net-One.de ([195.50.164.1]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA4GkHq71752 for ; Sun, 4 Nov 2001 11:46:18 -0500 (EST) (envelope-from knut.suebert@web.de) Received: from cascal.vtb (root@localhost [127.0.0.1]) by localhost (8.11.3/8.11.3/Debian 8.11.2-1) with ESMTP id fA4H9PI28188 for ; Sun, 4 Nov 2001 18:09:25 +0100 Received: from ksueber by cascal.vtb with local (Exim 3.32 #1 (Debian)) id 160Qfq-0000eb-00 for ; Sun, 04 Nov 2001 18:03:06 +0100 Date: Sun, 4 Nov 2001 18:03:06 +0100 To: pgsql-sql@postgresql.org Subject: Re: Recursive select Message-ID: <20011104180306.A2309@cascal.vtb> References: <20011029154754.A11730@cc.usu.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.3.18i X-No-Archive: yes From: knut.suebert@web.de X-Archive-Number: 200111/41 X-Sequence-Number: 5425 --CELKO-- schrieb: > Another way of representing trees is to show them as nested sets. Good evening, that's what I needed! To limit the result to entries below one node, I'd use something like SELECT P1.lft, COUNT(P2.emp) AS indentation, P1.emp FROM Personnel AS P1, Personnel AS P2 WHERE P1.lft BETWEEN P2.lft AND P2.rgt AND p1.lft>(SELECT lft FROM personnel WHERE emp='Chuck') AND p1.rgt<(SELECT rgt FROM personnel WHERE emp='Chuck') GROUP BY P1.emp, p1.lft ORDER BY P1.lft; lft | indentation | emp -----+-------------+------------ 5 | 3 | Donna 7 | 3 | Eddie 9 | 3 | Fred (3 rows) for emp='Albert' it returns lft | indentation | emp -----+-------------+------------ 2 | 2 | Bert 4 | 2 | Chuck 5 | 3 | Donna 7 | 3 | Eddie 9 | 3 | Fred (5 rows) My question is, how to limit this result to (Albert's indentation)+1? Thanks, Knut Sübert