From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Medi Montaseri <montaseri(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Query question |
Date: | 2008-05-22 21:50:16 |
Message-ID: | 20080522143344.D92116@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, 22 May 2008, Medi Montaseri wrote:
> Hi,
> I can use some help with the following query please.
>
> Given a couple of tables I want to do a JOIN like operation. Except that one
> of the columns might be null.
>
> create table T1 ( id serial, name varchar(20) );
> create table T2 ( id serial, name varchar(20) );
> create table T1_T2 ( id serial, t1_id integer not null , t2_id integer );
>
> Now I'd like to show a list of records from T1_T2 but reference T1 and T2
> for the names instead of IDs. But T1_T2.t2_id might be null
>
> select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2
> where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id
What would you want it to do if T1_T2.t2_id has a value that isn't in T2?
And should it do it for both T2 and T1? If using a NULL name is okay for
both, you can look at outer joins, something like:
select T1_T2.id, T1.name, T2.name from
T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id)
left outer join T2 on (T1_T2.t2_id = T2.id)
T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) will for example give
you a row even if there's not a row in T1 with T1.id being the same as
T1_T2.t1_id. In that case, you'll get the fields from T1_T2 and NULLs for
the fields from T1. The same between that table and T2 occurs with the
second outer join.
From | Date | Subject | |
---|---|---|---|
Next Message | Medi Montaseri | 2008-05-22 22:23:21 | Re: Query question |
Previous Message | Medi Montaseri | 2008-05-22 21:28:43 | Query question |