Solved

Detect differents between two tables

Posted on 2003-11-27
15
279 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
[X]
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
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 27

Expert Comment

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

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 23

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 23

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 23

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 23

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

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!

Question has a verified solution.

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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

624 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