Detect differents between two tables

I try to get just the different records between Table1 and Table2 ( that are in Table1 and not in Table2). How to do that?

I read now any record from Table1 and try to locate it in Table2. If not the I add it to the list.
But I think that can be done somehow else using Query.
LVL 9
ginsonicAsked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
select * from table1
minus
select * from table2

shows the records which differs on table1 to table2

select * from table2
minus
select * from table1

shows the records which differs on table2 to table1

the correct minus-syntax depends on your database

meikl ;-)
0
 
kretzschmarCommented:
o, the structure of both table must be the same, of course
0
 
ginsonicAuthor Commented:
I try to use sql := 'select * from Database\asig1.dbf minus select * from Database\asig2.dbf';
But get  'Invalid use of keyword.Token = select'
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
kretzschmarCommented:
seems localSQL does not support minus,sorry :-(
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
mmm...BTW you can use a left join looking for a null value in the same field...

this is the syntax:

SELECT * FROM [table1] LEFT JOIN [table2] ON [table1].[ID] = [table2].[ID]
WHERE ((([table2].[ID]) Is Null));

F68 ;-)
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
that's one's the MS Access syntax....using TQuery just remove the '[' and ']'... :))
0
 
kretzschmarCommented:
should it not be "left outer join", f68?
0
 
Ferruccio AccalaiConnect With a Mentor Senior developer, analyst and customer assistance Commented:
yes meikl you're right....as that was an Access syntax example (i usually use it for simple database applications) we know that it don't use a pure sql language....in particular with Access 97 for joins its syntax is [LEFT | RIGHT] JOIN

BTW of course the TQuery SQL syntax should be:

select * from table1 left outer join table2 on table1.id = table2.id where table2.id is null




0
 
ginsonicAuthor Commented:
I use:

sql:='select * from Database\asig1.dbf left outer join Database\asig2.dbf on Database\asig1.dbf.CNP = Database\asig2.dbf.CNP where Database\asig2.dbf.CNP is null';

My DBGrid show the both tables fields ( in the header ) and an empty record.
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
i'd do this:

with Query1 do
   begin
      If active then close;
      DatabaseName := 'c:\..\Database'; //this should be the database path
      sql.clear;
      sql.add('select * from asig1 left outer join asig2 on asig1.CNP = asig2.CNP where asig2.CNP is null');
      open;
   end;

.dbf extension is not required in sql....and not that asig.dbf.cnp coulb be point sql to a foreign field....
0
 
ginsonicAuthor Commented:
This method spend more time that my method ( locate one ).
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
are these tables indexed? if so you can use TTable.batchmove to appendupdate records from asig1 to asig2

0
 
ginsonicAuthor Commented:
Yes, are indexed. Can you tell me more a bout batchmove?
I wish just to get a list of difference and not to update my tables.
0
 
roknjohnCommented:
how about

SELECT * FROM Table1 WHERE NOT EXISTS (SELECT * FROM Table2)
0
 
roknjohnConnect With a Mentor Commented:
oops, i left off the second where clause...

SELECT * FROM Table1  
  WHERE NOT EXISTS (
               SELECT * FROM Table2
                 WHERE Table1.ID=Table2.ID)
0
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.

All Courses

From novice to tech pro — start learning today.