Link to home
Start Free TrialLog in
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)Flag for United States of America

asked on

Select Dupllicates with Condition

Three fields:

FieldA and FieldB are non-null text fields
FieldC is a bit that can be 0, 1, or NULL

I want to list all records where FieldA and FieldB are replicated, and, amongst any set of duplicate records, there is at least one record with FieldC set to 0 or NULL. I always want to see ALL duplicates, not just a grouped single record for each unique FieldA, FieldB pair. And I want to see ALL duplicates if ANY of the duplicates has a FieldC equal to 0 or NULL.

I have this so far but it doesn't handle FieldC:

    SELECT
            FieldA,
            FieldB
        INTO #DuplicateRecords
        FROM Table
        GROUP BY FieldA, FieldB
        HAVING COUNT(FieldA) > 1 AND COUNT(FieldB) > 1
   
    SELECT
            Table.FieldA,
            Table.FieldB,
            Table.SomeOtherField
        FROM Table
            INNER JOIN #DuplicateRecords ON
                #DuplicateRecords.FieldA = Table.FieldA
                    AND
                #DuplicateRecords.FieldB = Table.FieldB
   
    DROP TABLE #DuplicateRecords

Kevin
SOLUTION
Avatar of ggzfab
ggzfab

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial