From: David Johnston <polobo@yahoo.com>
To: ssylla <stefansylla@gmx.de>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: query two tables using same lookup table
Date: Mon, 23 Jul 2012 00:45:47 -0400
Message-ID: <68C10F74-B9BC-4D2A-9B5A-A2E935FEFA78@yahoo.com> (raw)
In-Reply-To: <1343012672084-5717583.post@n5.nabble.com>
References: <1343012672084-5717583.post@n5.nabble.com>
On Jul 22, 2012, at 23:04, ssylla <stefansylla@gmx.de> wrote:
> Dear list,
>
> assuming I have two tables as follows
>
> t1:
> id_project|id_auth
> 1|1
> 2|2
>
> t2:
> id_project|id_auth
> 1|2
> 2|1
>
>
> and a lookup-table:
>
> t3
> id_auth|name_auth
> 1|name1
> 2|name2
>
> Now I want to query t1 an t2 using the 'name_auth' column of lookup-table
> t3, so that I get the following output:
> id_project|name_auth_t1|name_auth_t2
> 1|name1|name2
> 2|name2|name1
>
> Any ideas?
>
> Thanks-
> Stefan
>
>
Not tested, may need minor syntax cleanup but the theory is sound.
With pj as (
Select id_project, id_name1, id_name2
From (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
From pj
Left join t3 as n1 on (id_auth1 = id_auth)
Left join t3 as n2 on (id_auth2 = id_auth)
;
Full join the two project tables and give aliases to the duplicate id_auth field. Then left join against t3 twice (once for eachid_auth) using yet a another set of aliases to distinguish them.
David J.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, stefansylla@gmx.de
Subject: Re: query two tables using same lookup table
In-Reply-To: <68C10F74-B9BC-4D2A-9B5A-A2E935FEFA78@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox