user1895076 user1895076 - 2 months ago 18
SQL Question

Oracle SQL - Comparing Rows

I have a problem I'm working on with Oracle SQL that goes something like this.

TABLE

PurchaseID CustID Location
----1------------1-----------A
----2------------1-----------A
----3------------2-----------A
----4------------2-----------B
----5------------2-----------A
----6------------3-----------B
----7------------3-----------B


I'm interested in querying the Table to return all instances where the same customer makes a purchase in different locations. So, for the table above, I would want:

OUTPUT

PurchaseID CustID Location
----3------------2-----------A
----4------------2-----------B
----5------------2-----------A


Any ideas on how to accomplish this? I haven't been able to think of how to do it, and most of my ideas seem like they would be pretty clunky. The database I'm using has 1MM+ records, so I don't want it to run too slowly.

Any help would be appreciated. Thanks!

Answer
SELECT *
FROM YourTable T
WHERE CustId IN (SELECT CustId
                 FROM YourTable
                 GROUP BY CustId
                 HAVING MIN(Location) <> MAX(Location))