Is this query possible? Please Help!

[Dusty Reagan]

I'm trying to create a query that finds rows w/ duplicate
"ContactKey s" then finds duplicate "AddressLin e1s" out of the list of
duplicate "ContactKey s." (I tried subqueries but it was really slow)

I am trying to create a new table with only duplicate ContactKey rows,
and then I wanted to use that table to pick out the duplicate
AddressLine1 rows.

****** BEGIN CODE **************
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)

SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;

drop table dupContactKeys
****** END CODE *************** **

This of course doesn't work. Please help, as I am going slightly mad!

Dusty

[ByteMyzer]

In the query-subquery scenario, have you tried:

SELECT C1.*
FROM Contacts AS C1
WHERE EXISTS (
SELECT C2.ContactKey
FROM Contacts AS C2
GROUP BY C2.ContactKey
HAVING C2.ContactKey=C 1.ContactKey AND Count(C2.Contac tKey)>1;)
AND EXISTS (
SELECT C3.AddressLine1
FROM Contacts AS C3
WHERE C3.ContactKey=C 1.ContactKey
GROUP BY C3.AddressLine1
HAVING C3.AddressLine1 =C1.AddressLine 1 AND Count(C3.Addres sLine1)>1;)
ORDER BY ContactKey, TypeKey;
See if this works for you.

"Dusty Reagan" <no*******@yaho o.com> wrote in message
news:5a******** *************** ***@posting.goo gle.com...

I'm trying to create a query that finds rows w/ duplicate
"ContactKey s" then finds duplicate "AddressLin e1s" out of the list of
duplicate "ContactKey s." (I tried subqueries but it was really slow)

I am trying to create a new table with only duplicate ContactKey rows,
and then I wanted to use that table to pick out the duplicate
AddressLine1 rows.

****** BEGIN CODE **************
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)

SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;

drop table dupContactKeys
****** END CODE *************** **

This of course doesn't work. Please help, as I am going slightly mad!

Dusty