pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

query two tables using same lookup table
2+ messages / 2 participants
[nested] [flat]

* query two tables using same lookup table
@ 2012-07-23 03:04 ssylla <stefansylla@gmx.de>
  2012-07-23 04:45 ` Re: query two tables using same lookup table David Johnston <polobo@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: ssylla @ 2012-07-23 03:04 UTC (permalink / raw)
  To: pgsql-sql

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



--
View this message in context: http://postgresql.1045698.n5.nabble.com/query-two-tables-using-same-lookup-table-tp5717583.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: query two tables using same lookup table
  2012-07-23 03:04 query two tables using same lookup table ssylla <stefansylla@gmx.de>
@ 2012-07-23 04:45 ` David Johnston <polobo@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: David Johnston @ 2012-07-23 04:45 UTC (permalink / raw)
  To: ssylla <stefansylla@gmx.de>; +Cc: pgsql-sql

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.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2012-07-23 04:45 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-23 03:04 query two tables using same lookup table ssylla <stefansylla@gmx.de>
2012-07-23 04:45 ` David Johnston <polobo@yahoo.com>

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