MSSQL Query...having some trouble

[Kaos]

Im trying to cobble together what I think would be an easy query but Im having a hard time..what Im looking for is just a list of all the duplicate entries

select contact_code, company_code 
from contacts
where contact_code not unique

Ive tried using != instead of not as well...
Im just a network guy who gets stuck fixing stuff out of my realm but i still like to try to figure it out on my own.

I found a way to get a count of the duplicates in my google searches

select contact_code,
count (contact_code) as NumOccurences
from contacts
group by  contact_code
having (Count(contact_code) > 1)

but that just says

there are 51 occurences of 0

i actually want to see them all line by line

does anyone have some advice, it would be greatly appreciated.

 

[Bohica69]

Try this - Assuming you've got an ID somewhere

SELECT ID, Contact_Code, Company
FROM Contacts C1
WHERE EXISTS ( SELECT ID FROM Contacts C2 WHERE C2.Contact_Code = C1.Contact_Code AND C2.Company=C1.Company AND C1.ID <> C2.ID)

What's happening here, is your saying, get me the rows from contacts (c1) where there is a matching row in contacts (C2) and the ID's of the records aren't the same.. If you don't have some tie breaker to identify that one row is different from the other, this query obviously won't work, but you can add an Identity column to the table and that will give you a tiebreaker.

 

[Cardboard Hammer]

If I understand you correctly, you can get what you want by combining (pun avoided) parts of your 2 queries together like so:

select C.contact_code, C.company_code 
from contacts C
INNER JOIN
(
select contact_code
from contacts
group by  contact_code
having (Count(contact_code) > 1)
) X
ON C.contact_code = X.contact_code