X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 571F19FA605
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  2 Jul 2006 09:52:29 -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 55634-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  2 Jul 2006 09:52:23 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from buniche.hst.terra.com.br (buniche.hst.terra.com.br
	[200.176.10.197])
	by postgresql.org (Postfix) with ESMTP id 1E74C9FA50D
	for <pgsql-sql@postgresql.org>; Sun,  2 Jul 2006 09:52:22 -0300 (ADT)
Received: from camenana.hst.terra.com.br (camenana.hst.terra.com.br
	[200.176.10.12])
	by buniche.hst.terra.com.br (Postfix) with ESMTP id 84BD43DD8025;
	Sun,  2 Jul 2006 09:52:20 -0300 (BRT)
X-Terra-Karma: -2%
X-Terra-Hash: 44cf59f5fdb5e67f750e63737669a0db
Received-SPF: pass (camenana.hst.terra.com.br: domain of terra.com.br
	designates 200.176.10.12 as permitted sender)
	client-ip=200.176.10.12; envelope-from=carlosreimer@terra.com.br;
	helo=reimer;
Received: from reimer (unknown [201.2.193.237])
	(authenticated user carlosreimer@terra.com.br)
	by camenana.hst.terra.com.br (Postfix) with ESMTP id 8108F4E0067;
	Sun,  2 Jul 2006 09:52:19 -0300 (BRT)
Reply-To: <carlosreimer@terra.com.br>
From: "Carlos H. Reimer" <carlosreimer@terra.com.br>
To: "Richard Broersma Jr" <rabroersma@yahoo.com>
Cc: <pgsql-sql@postgresql.org>
Subject: RES: Left join?
Date: Sun, 2 Jul 2006 09:52:35 -0300
Message-ID: <PEEPKDFEHHEMKBBFPOOKOEFIDCAA.carlosreimer@terra.com.br>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
In-Reply-To: <20060701214445.36616.qmail@web31805.mail.mud.yahoo.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
Importance: Normal
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/12
X-Sequence-Number: 25586

It´s just want I need! Perfect! Thanks!

Carlos

> -----Mensagem original-----
> De: Richard Broersma Jr [mailto:rabroersma@yahoo.com]
> Enviada em: sábado, 1 de julho de 2006 18:45
> Para: carlosreimer@terra.com.br; pgsql-sql@postgresql.org
> Assunto: Re: [SQL] Left join?
>
>
> > In the following table, codsol, codate and codfec are foreign keys
> > referencing table func and I need some help to codify a  SELECT
> command that
> > produces the following result set but instead of codsol, codate
> and codfec I
> > need the respectives names (column nome from table func).
> >
> > postgres=# select * from reqtran;
> >  codreq | codsol | codate | codfec
> > --------+--------+--------+--------
> >       1 |        |        |
> >       2 |      1 |        |
> >       3 |      1 |      1 |
> >       4 |      1 |      1 |      1
> > postgres=# \d func
>
> >      Table "public.func"
> >  Column |  Type   | Modifiers
> > --------+---------+-----------
> >  codfun | integer | not null
> >  nome   | text    |
>
> > Indexes:
> >     "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun)
> >     "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun)
> >     "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun)
>
> Would this do what you need?
>
> select R1.codreq,
>        CS.nome,
>        CD.nome,
>        CF.nome
> from rectran as R1
>    left join func as CS on (R1.codsol=CS.codefun)
>    left join func as CD on (R1.codate=CD.codefun)
>    left join func as CF on (R1.codfec=CF.codefun)
> ;
>
> Regards,
>
> Richard Broersma Jr.
>