Re: sql comparison and flag
From: Anna C. Dent <anacdent_at_hotmail.com>
Date: Mon, 27 Oct 2003 16:18:00 -0800
Message-ID: <ZAinb.87065$Ms2.61445_at_fed1read03>
>>bigbinc wrote:
>>
>>>I need to compare two tables and print the differences(vbscript). Is
>>>there a quick sql way to do this. Otherwise I have a O(n^2) problem.
>>>
>>>What I am doing now is getting the data from the 2nd table first and
>>>then printing the 1st table and checking if the value matches the 2nd
>>>table. O(n^2)
>>>
>>>lets say I have ids
>>>
>>>Get Table 2 data
>>>
>>>3 4 5
>>>
>>>Print Table 1 and Flag:
>>>
>>>1 - no O(n)
>>>2 - no O(n)
>>>3 - yes O(n)
>>>4 - yes O(n)
>>>5 - yes O(n)
>>>6 - no O(n)
>>>
>>>Is there a better way?
>>
>>Define better.
>>
>>(SELECT * FROM TABLE1
>>MINUS
>>SELECT * FROM TABLE2)
>>UNION
>>(SELECT * FROM TABLE2
>>MINUS
>>SELECT * FROM TABLE1)
Date: Mon, 27 Oct 2003 16:18:00 -0800
Message-ID: <ZAinb.87065$Ms2.61445_at_fed1read03>
bigbinc wrote:
> "Anna C. Dent" <anacdent_at_hotmail.com> wrote in message news:<BlCmb.84886$Ms2.45276_at_fed1read03>... >
>>bigbinc wrote:
>>
>>>I need to compare two tables and print the differences(vbscript). Is
>>>there a quick sql way to do this. Otherwise I have a O(n^2) problem.
>>>
>>>What I am doing now is getting the data from the 2nd table first and
>>>then printing the 1st table and checking if the value matches the 2nd
>>>table. O(n^2)
>>>
>>>lets say I have ids
>>>
>>>Get Table 2 data
>>>
>>>3 4 5
>>>
>>>Print Table 1 and Flag:
>>>
>>>1 - no O(n)
>>>2 - no O(n)
>>>3 - yes O(n)
>>>4 - yes O(n)
>>>5 - yes O(n)
>>>6 - no O(n)
>>>
>>>Is there a better way?
>>
>>Define better.
>>
>>(SELECT * FROM TABLE1
>>MINUS
>>SELECT * FROM TABLE2)
>>UNION
>>(SELECT * FROM TABLE2
>>MINUS
>>SELECT * FROM TABLE1)
> > > Well, lets just say I have 200 records in one table and 200 records in > another table, that is 200 * 200 calculations. Between > vbscript,oracle, my-brain, and somebody else's brain, there is > probably a better way.
There is a better way,
but you have to think smarter rather than a brute force.
If you can read from table1 & table2 in the same order, then only 1 full table scan of each table is required. Received on Tue Oct 28 2003 - 01:18:00 CET