query help

[tamilselvan]

NO UNION - NO NOTHING.

Option - 1 : In both tables all emp_no are found.
             In this case, use equijoin.

08:05:44 SQL> select * from emp1;

    EMP_NO LUT
---------- --------------------
        10 12-SEP-2004 08:04:02
        20 12-SEP-2004 08:04:06
        30 02-SEP-2004 08:04:25

08:05:50 SQL> select * from emp2 ;

    EMP_NO LUT
---------- --------------------
        10 09-AUG-2004 08:05:21
        20 30-AUG-2004 08:05:32
        30 10-SEP-2004 08:05:42

08:05:56 SQL> select a.emp_no, greatest(a.lut, b.lut)
08:07:26   2  from emp1 a, emp2 b
08:07:32   3  where a.emp_no = b.emp_no ;

    EMP_NO GREATEST(A.LUT,B.LUT
---------- --------------------
        10 12-SEP-2004 08:04:02
        20 12-SEP-2004 08:04:06
        30 10-SEP-2004 08:05:42

OPTION - 2 : EMP2 table has less emp_no.
             Then, you need to outer join.

08:07:40 SQL> delete emp2 where emp_no = 20 ;

1 row deleted.

08:08:43 SQL> commit;

Commit complete.

  1  select a.emp_no, greatest(a.lut, nvl(b.lut,to_date('01-01-1800','DD-MM-YYYY') ))
  2   from emp1 a, emp2 b
  3* where a.emp_no = b.emp_no(+)
08:13:20 SQL> /

    EMP_NO GREATEST(A.LUT,NVL(B
---------- --------------------
        10 12-SEP-2004 08:04:02
        20 12-SEP-2004 08:04:06
        30 10-SEP-2004 08:05:42

Option - 3 full outer join 

09:03:40 SQL> select * from emp1;

    EMP_NO LUT
---------- -------------------
        10 12/09/2004 08:04:02
        20 12/09/2004 08:04:06

09:08:11 SQL> select * from emp2 ;

    EMP_NO LUT
---------- -------------------
        10 09/08/2004 08:05:21
        30 10/09/2004 08:05:42

09:03:33 SQL> get emp.sql
  1  select nvl(a.emp_no, b.emp_no),
  2         greatest( nvl(a.lut, to_Date('01/01/1800','dd/mm/yyyy')) ,
  3                   nvl( b.lut, to_date('01/01/1800','dd/mm/yyyy'))
  4                 )
  5  from emp1 a full outer join emp2 b
  6* on a.emp_no = b.emp_no
09:03:38 SQL> /

NVL(A.EMP_NO,B.EMP_NO) GREATEST(NVL(A.LUT,
---------------------- -------------------
                    10 12/09/2004 08:04:02
                    20 12/09/2004 08:04:06
                    30 10/09/2004 08:05:42 

I would suggest to use FULL OUTER JOIN method, if both tables have diff number of rows.

Tamil