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....tp5717583.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

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

On Jul 22, 2012, at 23:04, ssylla <stefansylla (AT) gmx (DOT) de> wrote:

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 (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql