X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D274A3CB1A9 for ; Thu, 16 Dec 2004 21:23:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06160-04 for ; Thu, 16 Dec 2004 21:23:21 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id D03D03CB192 for ; Thu, 16 Dec 2004 21:23:21 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 7BD5431959; Thu, 16 Dec 2004 22:23:21 +0100 (MET) From: Marcus Krause X-Newsgroups: pgsql.sql Subject: get branches & childs from database Date: Thu, 16 Dec 2004 22:23:23 +0100 Organization: Hub.Org Networking Services Lines: 66 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de-AT; rv:1.7.3) Gecko/20040910 X-Accept-Language: de, de-de, de-at, en, en-us, fr-fr, es-es X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.516 tagged_above=0 required=5 tests=FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200412/130 X-Sequence-Number: 19750 Hello newsgroup! I'm trying to build up a menu by sending ONE query to database. Afterwards the result is used by PEAR::HTML_Menu get a html-structered menu. db-structure of gallery: +----+-------+------+------+--------+-------+ | id | title | date | root | parent | level | +----+-------+------+------+--------+-------+ | 5 | A | XX | 5 | 0 | 1 | | 1 | A2 | XX | 5 | 5 | 2 | | 2 | A1 | XX | 5 | 5 | 2 | | 3 | A11 | XX | 5 | 2 | 3 | | 4 | A12 | XX | 5 | 2 | 3 | | 6 | A21 | XX | 5 | 1 | 3 | | 7 | A211 | XX | 5 | 6 | 4 | | 8 | B | XX | 8 | 0 | 1 | | 9 | B1 | XX | 8 | 8 | 2 | +----+-------+------+------+--------+-------+ following limitations are set: - for root-nodes parent=0 have to be set - result should by available after sending ONE query - child of the specified gallery should be delivered - branch of the specified gallery should be delivered menu-structure after the query for id=6: + A |-> A1 (optional, no need to be but nice to have) |-> A2 |-> A21 (specified id) |-> A211 + B Therefore I need the following result after sending the query: +----+-------+--------+ | id | title | parent | +----+-------+--------+ | 8 | B | 0 | | 6 | A21 | 1 | | 7 | A211 | 6 | | 1 | A2 | 5 | | 5 | A | 0 | | 2 | A1 | 5 | (optional) +----+-------+--------+ I currently use following query: SELECT id,title,parent,FROM gallery WHERE (id=root OR root IN (SELECT root FROM gallery WHERE id=6)) AND gall_level <= (SELECT level FROM gallery WHERE id=6)+1 ORDER BY level ASC,date DESC With this query I get the additional branch under A1, but that's not what I want. I don't know what to do. I'm able to use views, subselects, rules (of course) and furthermore functions (plpgsql). I'd be pleased, if there are any ideas Thanks, Marcus.