Comparing a Field in Two Tables

Hello.

How does one compare two tables, whereby a similar field exists in 
both, in order to determine which records are missing in the smaller 
table?

Thanks in advance for your assistance.


-------------------

In article <MPG.21b2c0c2f86c85fb989697@news.dbase.com>, 
cecil@outoutdamnspam.ca says...
> Hello.
> 
> How does one compare two tables, whereby a similar field exists in 
> both, in order to determine which records are missing in the smaller 
> table?
> 
> Thanks in advance for your assistance.
> 
> 
> -------------------
> Regards, Cecil
> 


Cecil,

I would use some SQL something like this:

> select * from largeTable t where t.id not in( select s.id from smallTable s
)

This should show you the rows in the large table which do not exist in 
the small one. "id" is the key field which the two tables share in 
common.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

In article <MPG.21b2ee978786f342989d14@news.dbase.com>, 
gswassREMOVE_ME@attglobal.net says...
> 
> Cecil,
> 
> I would use some SQL something like this:
> 
> > select * from largeTable t where t.id not in( select s.id from
smallTable s )
> 
> This should show you the rows in the large table which do not exist in 
> the small one. "id" is the key field which the two tables share
in 
> common.
> 
---------------
Thanks Geoff.  I had completely forgotten about the use of 
subqueries.  I may call back here for some more advice on that.  The 
OLH for Local SQL has some information on using SELECT within the 
WHERE clause, but the larger issue of correlating several tables by 
using subqueries is still a learning experience.  Thanks again.

-------------------

In article <MPG.21b3b7dd156026fb989699@news.dbase.com>, 
cecil@outoutdamnspam.ca says...
> Thanks Geoff.  I had completely forgotten about the use of 
> subqueries.  I may call back here for some more advice on that.  The 
> OLH for Local SQL has some information on using SELECT within the 
> WHERE clause, but the larger issue of correlating several tables by 
> using subqueries is still a learning experience.  Thanks again.
> 
> -------------------
> Regards, Cecil


Cecil,

I'm glad it helped. Come back again, any time! 

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

In article <MPG.21b2ee978786f342989d14@news.dbase.com>, 
gswassREMOVE_ME@attglobal.net says...
> 
> I would use some SQL something like this:
> 
> > select * from largeTable t where t.id not in( select s.id from
smallTable s )
> 
> This should show you the rows in the large table which do not exist in 
> the small one. "id" is the key field which the two tables share
in 
> common.
> 
----------------
In my model, LargeTable has 22,000 records and SmallTable has 12,000 
records.  I created an SQL file to contain the statements and hooked 
it up to a query object.  It took a huge amount of time to process 
(even though I have new hardware), but at least a result was 
generated.

So on my second test I directly specified the statement in the 'sql' 
property of the query.  dBASE eventually hung when I ran the 
datamodule in dQuery.

Is there a different OODML approach to accomplish this which would be 
faster, yet as elegant?  Is the problem that I'm using this complex 
SQL statement on local dBASE tables?

I certainly like the concept you have recommended for me because I 
eventually intend to migrate the app to run off MS-SQL Server instead 
of the BDE.  But at the moment, I seem to be stuck.

--