On Jul 22, 2012, at 23:04, ssylla <stefansy...@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
