Link to home
Start Free TrialLog in
Avatar of dkilby
dkilbyFlag for Canada

asked on

ORACLE SQL DEVELOPER + Compare Tables

I have two tables and want to pull records from table A when a matching row is not in table B

But i need to look at three fields - so OrderNumber, OrderDate, CustomerID would need to be in Table A but not in Table B

lastly, for OrderDate I need to just check date and hour between the two as there is a slight difference between the exact times

Is this possible, if so how?

Thanks
Avatar of HainKurt
HainKurt
Flag of Canada image

select * from table1
where (col1,col2,col3) not in (select col1,col2,col3 from table2)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
left join solution

select a.*
  from table1 a left join table2 b
    on a.col1=b.col1 and a.col2=b.col2 and a.col3 between b.col3-@t and b.col3+@t
 where b.id is null

Open in new window

Avatar of Geert G
odd how a sql server type of variable would work on oracle

i couldn't get this to work
User generated image
declare v_t as number;
set v_t := x / (24*3600);

where x is a number in seconds...

then use v_t in sql...

or just dont use parameter and write qry as

and a.col3 between b.col3-(x/(24*3600)) and b.col3+(x/(24*3600))

Open in new window