From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | ssylla <stefansylla(at)gmx(dot)de> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: query two tables using same lookup table |
Date: | 2012-07-23 04:45:47 |
Message-ID: | 68C10F74-B9BC-4D2A-9B5A-A2E935FEFA78@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Russell Keane | 2012-07-26 14:59:32 | FW: view derived from view doesn't use indexes |
Previous Message | ssylla | 2012-07-23 03:04:32 | query two tables using same lookup table |