Find Records That Occur in TABLE1.DBF but not in TABLE2.DBF

I have two large Visual Foxpro tables, TABLE1.DBF and TABLE2.DBF. Both tables have identical structure. I need a script/program that compares the two tables and identifies any records that are in TABLE1.DBF but are not in TABLE2.DBF.

There is not a single unique identifier key for each record in these tables, but the combination of 3 different fields makes a record unique: mm_key, op_key, and pl_key. Can someone help me with this? TIA!
garyknightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You need to use LEFT JOIN:
Select t1.mm_key, t1.op_key, t1.pl_key
FROM Table1 t1 LEFT JOIN Table2 ts 
ON t1.mm_key = t2.mm_key AND t1.op_key = t2.op_key AND t1.pl_key = t2.pl_key
WHERE t2.mm_key IS NULL AND t2.op_key IS NULL AND t2.pl_key IS NULL

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
Yes, LEFT JOIN is the common standard but VFP also offers non-standard column names independent solution :-)
SELECT * FROM ( ;
  SELECT *, SYS(2017, '', 0, 1) Exp_1 FROM Table1) xx ;
   WHERE Exp_1 NOT IN (SELECT SYS(2017, '', 0, 1) FROM Table2)

Open in new window

Of course, the speed for large tables will depend on existing indexes.
1
Olaf DoschkeSoftware DeveloperCommented:
As Pavel says speed depends on indexes, but his general solution can't make use of any index, despite you have indexes on SYS(2017,'',0).

If you index on an expression concatenating these three fields (numeric or date or other types will need to be converted to string in that expression to be able to concat), then you can also relate the two tables and BROWSE FOR EOF():

Create Cursor table1 (id i)
Append Blank
Append Blank
Replace id with 1

Create Cursor table2 (id i)
Append Blank

Index on id tag id

Select table1
Set Relation to id into table2
Browse for Eof("table2")

Open in new window

Table1 contains id 0 and 1, table2 contains id 0, browse displays id 1.

And as said, for three columns you just need an expression to concat them, eg  mm_key+op_key+pl_key, but if they all are int, then this will sum and not concatenate them, then you'll need BINTOC(mm_key)+BINTOC(op_key)+BINTOC(pl_key), the expression for a compound index depends on types.

Bye, Olaf.
0
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
SELECT *
FROM table1
WHERE STR(table1.mm_key,10)+STR(table1.op_key,10)+STR(table1.pl_key,10) NOT IN (SELECT STR(table2.mm_key,10)+STR(table2.op_key,10)+STR(table2.pl_key,10) FROM table2)

Open in new window


You can change 10 to the field length in the tables.
0
jrbbldrCommented:
One issue with the excellent SQL SELECT * approach described above is that it does not result in the Actual Records themselves.
Instead it results in a COPY of the records meeting the criteria.
Will that be a problem or not - I can't say.

Olaf's suggestion above using  SET RELATION will result in the Actual Records meeting the criteria being 'exposed'

Depending on what you want to do with the records, you might choose one approach over the other.

Good Luck
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.