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
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) 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 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.
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; 
id)