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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
