We help IT Professionals succeed at work.

Select Dupllicates with Condition

zorvek (Kevin Jones)
zorvek (Kevin Jones) used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Guess you can change the test for Null and 0 into <> 1, than this could work:

SELECT Tabel1.FieldA, Tabel1.FieldB, Tabel1.FieldC INTO duplicates
FROM Tabel1 INNER JOIN
(SELECT Tabel1.FieldA, Tabel1.FieldB, Count(Tabel1.FieldA) AS AantalVanFieldA, Count(Tabel1.FieldB) AS AantalVanFieldB
FROM Tabel1
WHERE (((Tabel1.FieldC)<>1))
GROUP BY Tabel1.FieldA, Tabel1.FieldB
HAVING (((Count(Tabel1.FieldA))>1) AND ((Count(Tabel1.FieldB))>1))) SubQ
 ON (Tabel1.FieldB = SubQ.FieldB) AND (Tabel1.FieldA = SubQ.FieldA);
Commented:
This should get what you need :

select t1.fieldA, t1.fieldB from Table	t1
	join 
	(select fieldA,fieldB from Table  t2
	group by fieldA, fieldB
	having count(fieldA) > 1 and COUNT(fieldB) > 1)
	as grouped	
	on t1.FieldA = grouped.FieldA and t1.FieldB = grouped.FieldB 
	where (t1.FieldC = 0 or t1.FieldC is null)
	group by t1.FieldA, t1.FieldB

Open in new window

Commented:
That is to get the distinct value of FieldA and FieldB, to get all the column values you can extend it so it becomes :

select tFinal.* from Table tFinal
	join
	(
	select t1.fieldA, t1.fieldB from Table	t1
	join 
	(select fieldA,fieldB from Table  t2
	group by fieldA, fieldB
	having count(fieldA) > 1 and COUNT(fieldB) > 1)
	as grouped	
	on t1.FieldA = grouped.FieldA and t1.FieldB = grouped.FieldB 
	where (t1.FieldC = 0 or t1.FieldC is null)
	group by t1.FieldA, t1.FieldB) as groupedWithZeroOrNull
	on
	tFinal.FieldA = groupedWithZeroOrNull.FieldA and tFinal.FieldB = groupedWithZeroOrNull.FieldB

Open in new window