Re: Retrieval of duplicates, possible??

Hi!

nbirvine_at_my-deja.com wrote:
>
> Hello,
>
> I have two DEEP tables (>3 millions rows each) called POLICY and
> COMMISSION_POLICY and in order to fix a data problem, I need to
> identify all the duplicate policy numbers in both tables.
>
> By doing SELECT POLICY NUMBERS from POLICY UNION SELECT POLICY NUMBERS
> from COMMISSION_POLICY, I will create a 3rd table containing the result
> set common to both tables, thereby EXCLUDING duplicates.
>
> I want to do the opposite: the result of my query should contain
> nothing but duplicate policy numbers found in both tables. Logically,
> it seems that I would need to use a MINUS operator of some sort. Is it
> possible? Can someone please teach me how?

How about this:

SELECT PolNum
 FROM Policy
 WHERE PolNum IN

    (SELECT PolNum
     FROM Commission_Policy);

This gives you all policy number from Policy that are also found in Commission_Policy.

hth,
Heinz Received on Thu May 25 2000 - 00:00:00 CEST