LinuxQuestions.org
Did you know LQ has a Linux Hardware Compatibility List?
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
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.

Notices

Reply
 
Search this Thread
Old 06-18-2002, 08:30 PM   #1
ngomong
Member
 
Registered: Apr 2002
Posts: 54

Rep: Reputation: 15
SQL query, comparing tables


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?
 
Old 06-19-2002, 08:18 AM   #2
kahuna
Member
 
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, 02:19 AM   #3
ngomong
Member
 
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 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.
 
Old 07-07-2002, 07:44 PM   #4
pickledbeans
Member
 
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:

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

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


Reply


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 07:58 PM
sql: highest value, comparing fields Ephracis Programming 6 06-01-2005 05:28 PM
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 09:45 AM
Massive SQL Query patpawlowski Programming 7 03-05-2004 04:24 PM
SQL: making queries from multiple tables ganninu Programming 1 01-08-2004 11:17 AM


All times are GMT -5. The time now is 05:21 AM.

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