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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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




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
roknjohnCommented:
oops, i left off the second where clause...

SELECT * FROM Table1  
  WHERE NOT EXISTS (
               SELECT * FROM Table2
                 WHERE Table1.ID=Table2.ID)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.