DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=QKcpX6nbp/wNQOHb+b7jpUOMPAeddJk6AY+a0pg/hNdV8o7sx8PPB9YOk2KIHm5VSx3iTyHZmolA+/pKwwhgmZmLtSZVjfrx8sP99IT7l4FmTiq+b7REsdROoMrjZzCC35WA5H4rRnJhImB1GPMFrkwfxVkpMZjZ1j2NKH1OGGk=
	;
References: <1343012672084-5717583.post@n5.nabble.com>
In-Reply-To: <1343012672084-5717583.post@n5.nabble.com>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <68C10F74-B9BC-4D2A-9B5A-A2E935FEFA78@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: query two tables using same lookup table
Date: Mon, 23 Jul 2012 00:45:47 -0400
To: ssylla <stefansylla@gmx.de>

On Jul 22, 2012, at 23:04, ssylla <stefansylla@gmx.de> wrote:

> Dear list,=20
>=20
> assuming I have two tables as follows=20
>=20
> t1:=20
> id_project|id_auth=20
> 1|1=20
> 2|2=20
>=20
> t2:=20
> id_project|id_auth=20
> 1|2=20
> 2|1=20
>=20
>=20
> and a lookup-table:=20
>=20
> t3=20
> id_auth|name_auth=20
> 1|name1=20
> 2|name2=20
>=20
> Now I want to query t1 an t2 using the 'name_auth' column of lookup-table
> t3, so that I get the following output:=20
> id_project|name_auth_t1|name_auth_t2=20
> 1|name1|name2=20
> 2|name2|name1=20
>=20
> Any ideas?=20
>=20
> Thanks-=20
> Stefan
>=20
>=20

Not tested, may need minor syntax cleanup but the theory is sound.

With pj as (
Select id_project, id_name1, id_name2
=46rom (select id_project, id_auth as id_auth1 from t1) s1
Natural Full outer join
(select id_project, id_auth as id_auth2 from t2) s2
)
Select pj.id_project, n1.name_auth, n2.name_auth
=46rom pj
Left join t3 as n1 on (id_auth1 =3D id_auth)
Left join t3 as n2 on (id_auth2 =3D id_auth)
;

Full join the two project tables and give aliases to the duplicate id_auth f=
ield.  Then left join against t3 twice (once for eachid_auth) using yet a an=
other set of aliases to distinguish them.

David J.