Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Detect differents between two tables

Posted on 2003-11-27
15
Medium Priority
?
280 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 1200 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
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!

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

722 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