Solved

Detect differents between two tables

Posted on 2003-11-27
15
277 Views
Last Modified: 2010-04-05
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.
0
Comment
Question by:ginsonic
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 300 total points
ID: 9833158
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9833167
o, the structure of both table must be the same, of course
0
 
LVL 9

Author Comment

by:ginsonic
ID: 9835147
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 9835176
seems localSQL does not support minus,sorry :-(
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9835891
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
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9835990
that's one's the MS Access syntax....using TQuery just remove the '[' and ']'... :))
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9836084
should it not be "left outer join", f68?
0
 
LVL 22

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 100 total points
ID: 9836491
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
 
LVL 9

Author Comment

by:ginsonic
ID: 9838122
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
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9838167
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
 
LVL 9

Author Comment

by:ginsonic
ID: 9838744
This method spend more time that my method ( locate one ).
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9838985
are these tables indexed? if so you can use TTable.batchmove to appendupdate records from asig1 to asig2

0
 
LVL 9

Author Comment

by:ginsonic
ID: 9841252
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
 
LVL 1

Expert Comment

by:roknjohn
ID: 9846742
how about

SELECT * FROM Table1 WHERE NOT EXISTS (SELECT * FROM Table2)
0
 
LVL 1

Assisted Solution

by:roknjohn
roknjohn earned 100 total points
ID: 9846762
oops, i left off the second where clause...

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question