Users have multiple phones. I want to see which numbers are duplicates, where the only difference is phone type. Phone type being like Home, Work, Cell etc.
I want to return the
UserName, PhoneNumber, PhoneType1, PhoneType2.
This is what I have:
SELECT n.UserName, np.PhoneNumber, (SELECT pt1.Code FROM CustomerInformation.PhoneType pt1 WHERE pt1.PhoneTypeID = np.PhoneTypeID) AS PhoneType1, (SELECT pt2.Code FROM CustomerInformation.PhoneType pt2 WHERE pt2.PhoneTypeID = (SELECT np2.PhoneTypeID FROM CustomerInformation.NamePhone np2 WHERE np2.PhoneNumber = np.PhoneNumber AND np2.Extension = np.Extension AND np2.PhoneTypeID <> np.PhoneTypeID AND np.NameID = np2.NameID)) AS PhoneType2 FROM CustomerInformation.NamePhone np JOIN CustomerInformation.Name n ON n.NameID = np.NameID WHERE np.PhoneNumber = (SELECT np2.PhoneNumber FROM CustomerInformation.NamePhone np2 WHERE np2.PhoneNumber = np.PhoneNumber AND np2.Extension = np.Extension AND np2.PhoneTypeID <> np.PhoneTypeID AND np.NameID = np2.NameID)
But this returns rows that are near duplicates. PhoneType1 and PhoneType2 get switched around, and that's it.
I can't group by the alias, and if I don't include it in the group by it throws an error.
I can't use distinct on the row, because it is distinct. I can't use distinct on only 3 columns.
I can see why it's returning the same row twice, it sees that each number matches the other but has a different phone type, so they both show. But I don't know how to fix it.
A typical result I'm getting now is something like
1 - NickDiazstockton, 111-1111, Home, Cell 2 - NickDiazstockton, 111-1111, Cell, Home
So in this case he has the same phone for home and cell. But I really don't need to see the second row, the first has all the info I need.