Thread: Query question

Hi,<br />I can use some help with the following query please.<br /><br />Given a couple of tables I want to do a JOIN
likeoperation. Except that one of the columns might be null.<br /><br />create table T1 ( id serial, name varchar(20)
);<br/> create table T2 ( id serial, name varchar(20) );<br />create table T1_T2 ( id serial, t1_id integer not null ,
t2_idinteger ); <br /><br />Now I'd like to show a list of records from T1_T2 but reference T1 and T2 for the names
insteadof IDs. But T1_T2.t2_id might be null<br /><br />select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2<br
/>whereT1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id<br /><br />Basically since t2_id might be null, the condition will
failand the query will fail<br /><br />thanks<br />Medi<br /> 

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 fromT1_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.

Thanks Stephan,<br /><br />My real DDL include a forign key reference to T2.id and since I am ok with NULL value then
the"left outer join" indeed have solved the problem.<br /><br />Thanks again<br />Medi<br /><br /><div
class="gmail_quote">On Thu, May 22, 2008 at 2:50 PM, Stephan Szabo <<a
href="mailto:sszabo@megazone.bigpanda.com">sszabo@megazone.bigpanda.com</a>>wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;"><divclass="Ih2E3d">On Thu, 22 May 2008, Medi Montaseri wrote:<br /><br /> > Hi,<br /> > I can use some help
withthe following query please.<br /> ><br /> > Given a couple of tables I want to do a JOIN like operation.
Exceptthat one<br /> > of the columns might be null.<br /> ><br /> > create table T1 ( id serial, name
varchar(20));<br /> > create table T2 ( id serial, name varchar(20) );<br /> > create table T1_T2 ( id serial,
t1_idinteger not null , t2_id integer );<br /> ><br /> > Now I'd like to show a list of records from T1_T2 but
referenceT1 and T2<br /> > for the names instead of IDs. But T1_T2.t2_id might be null<br /> ><br /> > select
T1_T2.id,T1.name , T2.name from T1, T2, T1_T2<br /> > where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id<br /><br
/></div>Whatwould you want it to do if T1_T2.t2_id has a value that isn't in T2?<br /> And should it do it for both T2
andT1? If using a NULL name is okay for<br /> both, you can look at outer joins, something like:<br /><div
class="Ih2E3d"><br/> select T1_T2.id, T1.name, T2.name from<br /></div> T1_T2 left outer join T1 on (T1_T2.t1_id =
T1.id)<br/>  left outer join T2 on (T1_T2.t2_id = T2.id)<br /><br /> T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id)
willfor example give<br /> you a row even if there's not a row in T1 with T1.id being the same as<br /> T1_T2.t1_id.
 Inthat case, you'll get the fields from T1_T2 and NULLs for<br /> the fields from T1. The same between that table and
T2occurs with the<br /> second outer join.<br /><br /></blockquote></div><br />