Welcome to the most active Linux Forum on the web.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.


  Search this Thread
Old 06-18-2002, 09:30 PM   #1
Registered: Apr 2002
Posts: 54

Rep: Reputation: 15
SQL query, comparing tables

Here's what I want to do:


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?
Old 06-19-2002, 09:18 AM   #2
Registered: Jun 2002
Location: Grand Rapids, MI
Distribution: Redhat, Slackware
Posts: 78

Rep: Reputation: 15
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
Old 06-26-2002, 03:19 AM   #3
Registered: Apr 2002
Posts: 54

Original Poster
Rep: Reputation: 15
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 WHERE IS NULL;

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

Anyway, that did the trick! Thanks again.
Old 07-07-2002, 08:44 PM   #4
Registered: Jun 2002
Location: Bailey, CO
Distribution: Slackware
Posts: 483

Rep: Reputation: 31
This would be a good canidate for the mysql mailing list
You can subscribe here:

And MySQL Docs here:


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL: tables and queries in school project cold Programming 1 10-29-2005 08:58 PM
sql: highest value, comparing fields Ephracis Programming 6 06-01-2005 06:28 PM
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 10:45 AM
Massive SQL Query patpawlowski Programming 7 03-05-2004 05:24 PM
SQL: making queries from multiple tables ganninu Programming 1 01-08-2004 12:17 PM > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 01:12 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration