Skip site navigation (1) Skip section navigation (2)

Re: query two tables using same lookup table

From: David Johnston <polobo(at)yahoo(dot)com>
To: ssylla <stefansylla(at)gmx(dot)de>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: query two tables using same lookup table
Date: 2012-07-23 04:45:47
Message-ID: 68C10F74-B9BC-4D2A-9B5A-A2E935FEFA78@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Jul 22, 2012, at 23:04, ssylla <stefansylla(at)gmx(dot)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.


In response to

pgsql-sql by date

Next:From: Russell KeaneDate: 2012-07-26 14:59:32
Subject: FW: view derived from view doesn't use indexes
Previous:From: ssyllaDate: 2012-07-23 03:04:32
Subject: query two tables using same lookup table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group