Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D540D9FB388 for ; Wed, 4 Oct 2006 12:44:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 73697-09 for ; Wed, 4 Oct 2006 12:44:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 178CB9FB37B for ; Wed, 4 Oct 2006 12:44:39 -0300 (ADT) Received: (qmail 25271 invoked by uid 500); 4 Oct 2006 15:54:59 -0000 Date: Wed, 4 Oct 2006 10:54:59 -0500 From: Bruno Wolff III To: Alexander Burbello Cc: Lista Postgres Subject: Re: Recursive use Message-ID: <20061004155459.GC23343@wolff.to> Mail-Followup-To: Bruno Wolff III , Alexander Burbello , Lista Postgres References: <4523B23A.3000506@yahoo.com.br> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4523B23A.3000506@yahoo.com.br> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.043 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/24 X-Sequence-Number: 23327 On Wed, Oct 04, 2006 at 10:08:10 -0300, Alexander Burbello wrote: > > I need to know if Postgres do recursive search and how can I do! There is a contrib module (tablefunc I think) that allows recursive queries. However in your example below, you don't actuall need recursion, just self joins of the table. > I will explain my problem. > > > table COOPERATIVE > code_cooperative int > code_coo_father int > > I can have 3 level by business rules > > 1 - Father > ----- 2 - Children > --------- 3 - Grandchildren > > > I would like to have a query asking who is father and granfather > select grandfather, father from COOPERATIVE where COD_COOPERATIVE = 3 > > Do the Postgres can solve this problem? > Could anybody help me? You can do something like the following (untested, beware of typos): SELECT a.code_cooperative as child, a.code_coo_father as father, b.code_coo_father as grandfather FROM cooperative a, cooperative b WHERE a.code_coo_father = b.code_cooperative AND a.code_cooperative = 3 ;