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
(oncefor eachid_auth) using yet a another set of aliases to distinguish them.
David J.