Re: Informix 4GL : Outer join

dean@asa.com.sg wrote: :I have 2 table as follow : : tms ( emp_no, date, hr ) : emp_master ( emp_no, name ) :I would like to create a 3rd table as follow : : join ( emp_no, name, date, hr ) :e.g. : tms emp_master : ( E2, 01/04/97, 8.4 ) ( E1, John ) : ( E3, 01/04/97, 8.4 ) ( E2, Albert ) : ( E4, 01/04/97, 8.4 ) ( E3, Christ ) : --> join : ( E1, John, , ) : ( E2, Albert, 01/04/97, 8.4) : ( E3, Christ, 01/04/97, 8.4) : ( E4, , 01/04/97, 8.4) :Wat is the most efficient way of joining the 2 tables ?? :I have to do this at least once a day .. You want a full outer join that Informix do not support directly. The following union will do it though. (Using union all would give a different result.) select tms.emp_no, name, edate, hr from tms, outer emp_master where tms.emp_no = emp_master.emp_no union select emp_master.emp_no, name, edate, hr from emp_master, outer tms where emp_master.emp_no = tms.emp_no This is inefficient in that it will retrieve all rows that exit in both tables twice and use the union to get rid of duplicates. I don't know another more efficient way of doing it. May be someone have better ideas? Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org