Detect differents between two tables

ginsonic
ginsonic used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2004
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 ;-)
Top Expert 2004

Commented:
o, the structure of both table must be the same, of course

Author

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'
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Top Expert 2004

Commented:
seems localSQL does not support minus,sorry :-(
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 ;-)
Ferruccio AccalaiSenior developer, analyst and customer assistance

Commented:
that's one's the MS Access syntax....using TQuery just remove the '[' and ']'... :))
Top Expert 2004

Commented:
should it not be "left outer join", f68?
Ferruccio AccalaiSenior 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




Author

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.
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....

Author

Commented:
This method spend more time that my method ( locate one ).
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

Author

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.

Commented:
how about

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

SELECT * FROM Table1  
  WHERE NOT EXISTS (
               SELECT * FROM Table2
                 WHERE Table1.ID=Table2.ID)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial