Comparing two tables

[spiff2002]

We got some data in two excel files that were imported into two diferent tables (t1 and t2). The tables have a lot of fields in common but they have no primary key nor unique ide. The tables basically contain sales data. I need to run a query where I compare the two tables and get the rows that are not common to the tables. There are pretty good chances that within the same table there are not repeated records

i.e

t1
cust# date qty sale_price item# brand
1 5-5-03 2 10.00 1 b1
2 5-7-03 5 20.00 1 b1
1 5-8-03 10 100.00 2 b2
1 5-6-03 8 80.00 3 b3

t2
cust# date qty sale_price item# brand
1 5-5-03 2 10.00 1 b1
2 5-7-03 5 20.00 1 b1
1 5-6-03 8 80.00 3 b3


The query will show the rows that are in table t1 but not in t2
so the output will be:


cust# date qty sale_price item# brand
1 5-8-03 10 100.00 2 b2

Thanks in advance for any help with this

 

[swampBoogie]

select * from t1 as q
where not exists
(select * from t2 
 where t2.cust# = q.cust#
   and t2.date = q.date
   and t2.qty = q.qty
   and t2.sale_price = q.sale_price
   and t2.item = q.item
   and t2.brand = q.brand)

 

[spiff2002]

Thanks agaian swampBoogie !

 

[dnoeth]

select * from t1
except
select * from t2

When there are repeated (duplicate) rows use EXCEPT ALL.

Some DBMSes use MINUS instead of EXCEPT.

Dieter

 

[Rachoor]

select cust#, date , qty, sale_price, item#, brand
from t1
minus
select cust#, date , qty, sale_price, item#, brand from t2

or

select * from t1
minus
select * from t2

when the table has duplicate rows then:
select distinct cust#, date , qty, sale_price, item#, brand
from t1
minus
select distinct cust#, date , qty, sale_price, item#, brand from t2