I'm the wimp, someone please help me with this...

I have two identical tables (as to structure), I want to use the following
subquery but it is, of course, illegal...

'SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
WHERE PRODID,CAS1,DESCRIPTION NOT IN (SELECT PRODID,CAS1,DESCRIPTION FROM
"table2")'

How can I do this ? BTW, I'm having to do this in DBIsam 3 as I am working
on an old legacy app
at the moment

Any help would be greatly appreciated !

John

John

>'SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
>WHERE PRODID,CAS1,DESCRIPTION NOT IN (SELECT PRODID,CAS1,DESCRIPTION FROM
>"table2")'

I wasn't using SQL much back then so I'm not even sure if DBISAM V3 supports subselects of any form.

With that preamble one main thought - convert the three columns into one by CASTing and adding together - assuming they're all VARCHARs


SELECT PRODID, CAS1, DESCRIPTION FROM "Table1"
WHERE PRODID+CAS1+DESCRIPTION NOT IN (SELECT PRODID+CAS1+DESCRIPTION FROM "table2")

If that doesn't work try selecting the table2 stuff into a memory table first and see if that works.

Roy Lambert [Team Elevate]

Roy,

V3 does allow subselects but it's the compounding WHERE
PRODID,CAS1,DESCRIPTION NOT IN
part it doesn't like.  The fields are all string fields , just different
lengths.

I will try your suggestion, thanks for replying !

John
"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:EA894AF8-8D34-49BA-A431-997090B15A1E@news.elevatesoft.com...
> John
>
>>'SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
>>WHERE PRODID,CAS1,DESCRIPTION NOT IN (SELECT PRODID,CAS1,DESCRIPTION FROM
>>"table2")'
>
> I wasn't using SQL much back then so I'm not even sure if DBISAM V3
> supports subselects of any form.
>
> With that preamble one main thought - convert the three columns into one
> by CASTing and adding together - assuming they're all VARCHARs
>
>
> SELECT PRODID, CAS1, DESCRIPTION FROM "Table1"
> WHERE PRODID+CAS1+DESCRIPTION NOT IN (SELECT PRODID+CAS1+DESCRIPTION FROM
> "table2")
>
> If that doesn't work try selecting the table2 stuff into a memory table
> first and see if that works.
>
> Roy Lambert [Team Elevate]
>

Roy,

Q.Prepare doesn't complain but Q.Open never returns with this query, just
hangs the app

SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
WHERE PRODID+CAS1+DESCRIPTION NOT IN (SELECT PRODID+CAS1+DESCRIPTION FROM
"Table2")

BTW, The primary index is PRODID + CAS1 + DESCRIPTION


John

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:EA894AF8-8D34-49BA-A431-997090B15A1E@news.elevatesoft.com...
> John
>
>>'SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
>>WHERE PRODID,CAS1,DESCRIPTION NOT IN (SELECT PRODID,CAS1,DESCRIPTION FROM
>>"table2")'
>
> I wasn't using SQL much back then so I'm not even sure if DBISAM V3
> supports subselects of any form.
>
> With that preamble one main thought - convert the three columns into one
> by CASTing and adding together - assuming they're all VARCHARs
>
>
> SELECT PRODID, CAS1, DESCRIPTION FROM "Table1"
> WHERE PRODID+CAS1+DESCRIPTION NOT IN (SELECT PRODID+CAS1+DESCRIPTION FROM
> "table2")
>
> If that doesn't work try selecting the table2 stuff into a memory table
> first and see if that works.
>
> Roy Lambert [Team Elevate]
>

Hi John

This should work for you.  I knocked this up using a DBISAM3 database.

SELECT 1 AS TableNo, FeeCode, Description
INTO MEMORY Temp
FROM fees1
UNION
SELECT 2 AS TableNo, FeeCode, Description
FROM fees2
;
SELECT MAX(TableNo), FeeCode, Description
FROM MEMORY Temp
GROUP BY FeeCode, Description
HAVING MAX(TableNo) = 1

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"John Taylor" <jcta@snappysoftware.com> wrote in message
news:8683B727-987C-461F-A253-D62CE519846E@news.elevatesoft.com...
> Roy,
>
> Q.Prepare doesn't complain but Q.Open never returns with this query, just
> hangs the app
>
> SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
> WHERE PRODID+CAS1+DESCRIPTION NOT IN (SELECT PRODID+CAS1+DESCRIPTION FROM
> "Table2")
>
> BTW, The primary index is PRODID + CAS1 + DESCRIPTION
>
>
> John
>
> "Roy Lambert" <roy@lybster.me.uk> wrote in message
> news:EA894AF8-8D34-49BA-A431-997090B15A1E@news.elevatesoft.com...
>> John
>>
>>>'SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
>>>WHERE PRODID,CAS1,DESCRIPTION NOT IN (SELECT PRODID,CAS1,DESCRIPTION FROM
>>>"table2")'
>>
>> I wasn't using SQL much back then so I'm not even sure if DBISAM V3
>> supports subselects of any form.
>>
>> With that preamble one main thought - convert the three columns into one
>> by CASTing and adding together - assuming they're all VARCHARs
>>
>>
>> SELECT PRODID, CAS1, DESCRIPTION FROM "Table1"
>> WHERE PRODID+CAS1+DESCRIPTION NOT IN (SELECT PRODID+CAS1+DESCRIPTION FROM
>> "table2")
>>
>> If that doesn't work try selecting the table2 stuff into a memory table
>> first and see if that works.
>>
>> Roy Lambert [Team Elevate]
>>

Thanks Jeff,

I will give it a try, the other way, the query took about 5 minutes

John

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:43FCA36F-3CCD-480C-ABD6-BA30636E64B5@news.elevatesoft.com...
> Hi John
>
> This should work for you.  I knocked this up using a DBISAM3 database.
>
> SELECT 1 AS TableNo, FeeCode, Description
> INTO MEMORY Temp
> FROM fees1
> UNION
> SELECT 2 AS TableNo, FeeCode, Description
> FROM fees2
> ;
> SELECT MAX(TableNo), FeeCode, Description
> FROM MEMORY Temp
> GROUP BY FeeCode, Description
> HAVING MAX(TableNo) = 1
>
> Cheers
>
> Jeff
> --
> Jeff Cook
> Aspect Systems Ltd
> www.aspect.co.nz
>
> "John Taylor" <jcta@snappysoftware.com> wrote in message
> news:8683B727-987C-461F-A253-D62CE519846E@news.elevatesoft.com...
>> Roy,
>>
>> Q.Prepare doesn't complain but Q.Open never returns with this query, just
>> hangs the app
>>
>> SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
>> WHERE PRODID+CAS1+DESCRIPTION NOT IN (SELECT PRODID+CAS1+DESCRIPTION FROM
>> "Table2")
>>
>> BTW, The primary index is PRODID + CAS1 + DESCRIPTION
>>
>>
>> John
>>
>> "Roy Lambert" <roy@lybster.me.uk> wrote in message
>> news:EA894AF8-8D34-49BA-A431-997090B15A1E@news.elevatesoft.com...
>>> John
>>>
>>>>'SELECT PRODID,CAS1,DESCRIPTION FROM "Table1"
>>>>WHERE PRODID,CAS1,DESCRIPTION NOT IN (SELECT PRODID,CAS1,DESCRIPTION
>>>>FROM
>>>>"table2")'
>>>
>>> I wasn't using SQL much back then so I'm not even sure if DBISAM V3
>>> supports subselects of any form.
>>>
>>> With that preamble one main thought - convert the three columns into one
>>> by CASTing and adding together - assuming they're all VARCHARs
>>>
>>>
>>> SELECT PRODID, CAS1, DESCRIPTION FROM "Table1"
>>> WHERE PRODID+CAS1+DESCRIPTION NOT IN (SELECT PRODID+CAS1+DESCRIPTION
>>> FROM "table2")
>>>
>>> If that doesn't work try selecting the table2 stuff into a memory table
>>> first and see if that works.
>>>
>>> Roy Lambert [Team Elevate]
>>>
>
>

Yikes!  5 minutes!

I can't imagine why it would take so long unless the tables are ginormous.  And if they are, then the MEMORY tables are probably a bad choice.  Perhaps an ORDER BY in the unioned SELECT using the primary key of the tables would help - just guessing though.  Also maybe UNION ALL would make a difference.

How about using LEFT OUTER JOIN instead.
-------------------------------------------------------------------------
SELECT T1.*, T2.PrimaryKey1
FROM Table1 T1
LEFT OUTER JOIN Table2 T2
  (ON (T2.PrimaryKey1 = T1.PrimaryKey1)  AND (T2.PrimaryKey2 = T1.PrimaryKey2) ...)
WHERE T2.PrimaryKey1 IS NULL
-------------------------------------------------------------------------

... that should be as quick as anything if the correct indices are in place.


Good Luck

Cheers

Jeff


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

"John Taylor" wrote:

Thanks Jeff,

I will give it a try, the other way, the query took about 5 minutes

John

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:43FCA36F-3CCD-480C-ABD6-BA30636E64B5@news.elevatesoft.com...
> Hi John
>
> This should work for you.  I knocked this up using a DBISAM3 database.
>
> SELECT 1 AS TableNo, FeeCode, Description
> INTO MEMORY Temp
> FROM fees1
> UNION
> SELECT 2 AS TableNo, FeeCode, Description
> FROM fees2
> ;
> SELECT MAX(TableNo), FeeCode, Description
> FROM MEMORY Temp
> GROUP BY FeeCode, Description
> HAVING MAX(TableNo) = 1
>
> Cheers
>
> Jeff
> --
> Jeff Cook
> Aspect Systems Ltd
> www.aspect.co.nz
>

Hi John

I just tested this:-
====================================
SELECT T1.*, T2.TransNo
FROM "TenantTrans" T1
LEFT OUTER JOIN "Copy of TenantTrans" T2 ON (T2.TransNo = T1.TransNo)
WHERE T2.TransNo IS NULL
====================================

... where TenantTrans and it's copy are each greater then 5MB (biggest table I had lying around) and TransNo is a secondary key.

It returned the one record that I had deleted manually from the copy in 0.531 secs in DBSYS.

I suspect that the progress display took as much time as the processing of the query

Cheers

Jeff Cook wrote:

Yikes!  5 minutes! ....

One of my posts went amiss, what I said later is your way takes about 1
second, very nice !

The other way (mine and Roy's discussion) was taking about 5 minutes.

Thank you !

John

"Jeff Cook" wrote in message
news:BC537F74-FDEB-4F4D-BDBA-F70D857C7E25@news.elevatesoft.com...
> Hi John
>
> I just tested this:-
> ====================================
> SELECT T1.*, T2.TransNo
> FROM "TenantTrans" T1
> LEFT OUTER JOIN "Copy of TenantTrans" T2 ON (T2.TransNo = T1.TransNo)
> WHERE T2.TransNo IS NULL
> ====================================
>
> .. where TenantTrans and it's copy are each greater then 5MB (biggest
> table I had lying around) and TransNo is a secondary key.
>
> It returned the one record that I had deleted manually from the copy in
> 0.531 secs in DBSYS.
>
> I suspect that the progress display took as much time as the processing of
> the query
>
> Cheers
>
> Jeff Cook wrote:
>
> Yikes!  5 minutes! ....
>

Hmmm.... something is happening to my posts

"Jeff Cook" wrote in message
news:BC537F74-FDEB-4F4D-BDBA-F70D857C7E25@news.elevatesoft.com...
> Hi John
>
> I just tested this:-
> ====================================
> SELECT T1.*, T2.TransNo
> FROM "TenantTrans" T1
> LEFT OUTER JOIN "Copy of TenantTrans" T2 ON (T2.TransNo = T1.TransNo)
> WHERE T2.TransNo IS NULL
> ====================================
>
> .. where TenantTrans and it's copy are each greater then 5MB (biggest
> table I had lying around) and TransNo is a secondary key.
>
> It returned the one record that I had deleted manually from the copy in
> 0.531 secs in DBSYS.
>
> I suspect that the progress display took as much time as the processing of
> the query
>
> Cheers
>
> Jeff Cook wrote:
>
> Yikes!  5 minutes! ....
>