On Jul 22, 2012, at 23:04, ssylla <stefansylla(at)gmx(dot)de> wrote:
> Dear list,
> assuming I have two tables as follows
> and a lookup-table:
> Now I want to query t1 an t2 using the 'name_auth' column of lookup-table
> t3, so that I get the following output:
> Any ideas?
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
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.
In response to
pgsql-sql by date
|Next:||From: Russell Keane||Date: 2012-07-26 14:59:32|
|Subject: FW: view derived from view doesn't use indexes|
|Previous:||From: ssylla||Date: 2012-07-23 03:04:32|
|Subject: query two tables using same lookup table|