[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] query two tables using same lookup table
From:       David Johnston <polobo () yahoo ! com>
Date:       2012-07-23 4:45:47
Message-ID: 68C10F74-B9BC-4D2A-9B5A-A2E935FEFA78 () yahoo ! com
[Download RAW message or body]

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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


[prev in list] [next in list] [prev in thread] [next in thread]