zorvek (Kevin Jones)
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.