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 ..
>
> Thanx. ..
> Chong Hock
One solution would be to use a UNION and let the DB remove the
duplicates:
SELECT e1.emp_no, name, date, hr
FROM emp_master e1, OUTER tms
WHERE e1.emp_no = tms.emp_no
UNION
SELECT tms.emp_no, name, date, hr
FROM tms, OUTER emp_master e1
WHERE e1.emp_no = tms.emp_no
S.W.
wilcoxon@pioneerplanet.infi.net
**********************************************************************
... this is only a test
... if this had been a real signature it would have been interesting
**********************************************************************