Re: query two tables using same lookup table

On Jul 22, 2012, at 23:04, ssylla <stefansylla(at)gmx(dot)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.