select records from table A not exist in tabe B 1

[R17]

hello!

how do you select records from table A that does not exist in table B?

i use SQL command like :

SELECT * FROM XA A WHERE NOT EXISTS (SELECT checkno FROM XM B WHERE a.checkno=b.checkno .AND. a.agycode=b.agycode .AND. a.brcode=b.brcode)

that works well but it is taking too long, like more than 5 mins.

both tables have the same structure, each has more than 50,000 records. im using foxpro for windows 2.6 on a win 98.

any help?

 

[rob444]

SELECT * FROM XA WHERE CHECKNO NOT IN (SELECT CHECKNO FROM XM)

That should give you every checkno that does not exist in XM.


Rob.

 

[rob444]

If you need to check all 3 variables:

SELECT * FROM XM WHERE CHECKNO+AGYCODE+BRCODE NOT IN ( SELECT CHECKNO+AGYCODE+BRCODE FROM XM )

or if checkno, agycode,brcode are numbers place the str(variable, length) function around each numerical variable.

Rob

 

[rob444]

And of course the first XM in the last example should be XA.

Rob.

 

[tesar]

To create difference two files, try
sele b
index on checkno+agycode+brcode tag klic
sele a
set rela to checkno+agycode+brcode into b
copy to differ for eof("b&quot
go top
... and measure time ...
Tesar

 

[R17]

hey rob44 thanks a lot!!! great help!