Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Detect differents between two tables

Posted on 2003-11-27
15
276 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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