Re: Query question

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 />