SQL query, comparing tables

ngomong · 06-18-2002, 08:30 PM

Here's what I want to do:

table1.records
table2.records

Each has approx 50,000 records, and they're supposed to be in sync The problem is, table2.records has 37 less records than table1.records.

What sort of SQL query can I run to compare the tables and determine which records are missing from table2?

kahuna · 06-19-2002, 08:18 AM

select <pri_key> from table_1 where <pri_key> not in (select <for_key> from table_2).

This assumes that you RDBMS supports subselects and that the two tables are properly related. The for_key means foreign key into table 1 pri_key is the primary key for table 1.

You can select the whole record, but since the primary key is unique, you can just list these, then make the adjustments at your lesiure

ngomong · 06-26-2002, 02:19 AM

Thanks for that hint! It pointed me in the right direction.

As it turned out, I couldn't do the subselect exactly like that. Instead, I formed my query like this:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

(That's from the mysql documentation... just didn't know where to look before.)

Anyway, that did the trick! Thanks again.

pickledbeans · 07-07-2002, 07:44 PM

This would be a good canidate for the mysql mailing list

You can subscribe here:

http://www.mysql.com/documentation/lists.html

And MySQL Docs here:
http://www.mysql.com/documentation/