CODEWITHSUNDEEP
sqlsql-server
Nisse
Nisse

Reputation: 11

Find any duplicate value in different columns SQL Server

I have a table with columns that looks like this.

id  phone1 phone2 Phone3 Phone4 phone5

I would like to find the id's where any of the phonenumbers match any other. I have googled and searched this site but did not find an answer.

Thank you in advance.

//Nisse

Upvotes: 1

Views: 83

Answers (4)

David Faber
David Faber

Reputation: 12486

You might use UNION ALL:

WITH cte AS (
    SELECT id, phone1 AS phone FROM mytable
     WHERE phone1 IS NOT NULL
     UNION ALL
    SELECT id, phone2 AS phone FROM mytable
     WHERE phone2 IS NOT NULL
     UNION ALL
    SELECT id, phone3 AS phone FROM mytable
     WHERE phone3 IS NOT NULL
     UNION ALL
    SELECT id, phone4 AS phone FROM mytable
     WHERE phone4 IS NOT NULL
     UNION ALL
    SELECT id, phone5 AS phone FROM mytable
     WHERE phone5 IS NOT NULL
)
SELECT id FROM (
    SELECT id, phone, COUNT(*) FROM cte
     GROUP BY id, phone
    HAVING COUNT(*) > 1
) dup;

Upvotes: 0

SQL Taylor
SQL Taylor

Reputation: 138

If you are using a Name-Value pair model, then all you have to do is UNPIVOT your table to get it to return data how you want it to look. If you are not using a Name-Value pair table, I would suggest rethinking the design of the table.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

One other way is using cross apply with table valued constructor

SELECT id
FROM   Yourtable
       CROSS apply (VALUES ('Phone1',Phone1),
                           ('Phone2',Phone2),
                           ('Phone3',Phone3),
                           ('Phone4',Phone4),
                           ('Phone5',Phone5)) cs(cname, data)
GROUP  BY id,
          data
HAVING Count(1) > 1

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460018

With a model like this you have to go this way:

SELECT id
FROM dbo.TableName
WHERE phone1 = phone2 
OR    phone1 = phone3
OR    phone1 = phone4
OR    phone1 = phone5
OR    phone2 = phone3
OR    phone2 = phone4
OR    phone2 = phone5
OR    phone3 = phone4
OR    phone3 = phone5
OR    phone4 = phone5

If you had another table where you store those phone-numbers which is linked via foreign-key you could do:

SELECT t.id
FROM dbo.TableName t INNER JOIN dbo.PhoneNumbers p
    ON t.ID = p.FkID
GROUP BY id
HAVING COUNT(p.PhoneNumber) <> COUNT(DISTINCT p.PhoneNumber)

Upvotes: 4

Related Questions