Solved

Detect differents between two tables

Posted on 2003-11-27
15
271 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now