Difference between 2 tables

[ashokinb]

Hi,

We have 2 tables as follows,

TableA
------------
doc_id ver_id ref_date

TableB
------------
doc_id ver_id ref_min_date

We need to find out the records that exists in TableA but not in TableB
and also to get the minimum of TableA.ref_date from the result.

For example,

TableA has the following rows
doc_id ver_id ref_date
100 1 10-AUG-2006
100 2 12-AUG-2006
100 2 13-AUG-2006
100 2 14-AUG-2006
100 2 15-AUG-2006
200 1 17-AUG-2006
300 1 18-AUG-2006

TableB has the following rows
doc_id ver_id ref_date
100 1 10-AUG-2006
200 1 10-AUG-2006

Result should be as follows
doc_id ver_id ref_date
100 2 12-AUG-2006
300 1 18-AUG-2006

Please help us in writing the query

Thanks
Ashok

[jsfromynr]

Hi Ashok,

You may like to try this.

Select A.doc_id,A.ver_id ,Min(A.ref_date) From TableA A
Where Not Exists ( Select 1 From TableB B where A.doc_id=B.doc_id
And A.ver_id=B.ver_id
And A.ref_date=B.ref_date
) Group By A.doc_id,A.ver_id ;

I hope this helps.

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com