-1

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.

  • 1
    People will find it a lot easier to answer your question if you edit it to provide CREATE TABLE statements for the tables involved, and some sample data in the form of INSERT statements. See how to provide a minimal reproducible example. – Paul White Reinstate Monica Dec 1 '19 at 17:45
0

Directly (maybe not optimal)

WITH cte AS (source tables joining)
SELECT t1.UserName, t1.PhoneNumber, t1.PhoneType PhoneType1, t2.PhoneType PhoneType2
FROM cte t1, cte t2
WHERE t1.UserName = t2.UserName
  AND t1.PhoneNumber = t2.PhoneNumber
  AND t1.PhoneType < t2.PhoneType
  • This seems to work. Adding in the t1.PhoneType < t2.PhoneType. Thanks for all the responses everyone, I'll be going through these to fully understand them. Never used WITH or Partition By before. – Steak514 Dec 1 '19 at 23:31
0

The following code probably isn't the most optimal but it will solve your issue:

For now I am going to make a test table as follows (as you didn't provide any):

CREATE TABLE T (
id int PRIMARY KEY IDENTITY(1,1),
phonenumber nvarchar(15),
phonetype1 nvarchar(100),
phonetype2 nvarchar(100)
)
GO

And I will fill it with this dummy test data:

INSERT INTO T
VALUES ('0661112333', 'type1', 'type2'),
('0622222222', 'type1', 'type2'),
('0633333333', 'type1', 'type2'),
('0633333333', 'type1', 'type3'),
('0612312312', 'type1', 'type2'),
('0645674563', 'type1', 'type2'),
('0600000000', 'type1', 'type2'),
('0600000000', 'type1', 'type2'),
('0661112333', 'type1', 'type2')
GO

^ based on your request, only

('0633333333', 'type1', 'type2')
('0633333333', 'type1', 'type3')

should be selected as they're the only 2 phone numbers with 2 different phone types. This query does that:

WITH CTE AS (
     SELECT phonenumber, 
                phonetype1, 
                phonetype2,
                DENSE_RANK() OVER (PARTITION BY phonenumber ORDER BY phonetype1, phonetype2) AS DR
     FROM T
)
SELECT  T.phonenumber,
          T.phonetype1, 
          T.phonetype2
FROM T 
     INNER JOIN CTE on CTE.phonenumber= T.phonenumber
WHERE DR > 1

^ this code will return ↓

phonenumber     phonetype1     phonetype2                                                                                       
--------------- ------------- ---------------
0633333333      type1          type2                                                                                            
0633333333      type1          type3                                                                                            

Here is the same code but applied to your tables (you will probably have to fix things if there are errors, consider the code below as if its pseudo code)

WITH CTE AS (
     SELECT NP.phonenumber, 
                PT.phonetype1, 
                PT.phonetype2,
                DENSE_RANK() OVER (PARTITION BY NP.phonenumber ORDER BY PT.phonetype1, PT.phonetype2) AS DR
     FROM CustomerInformation.NamePhone AS NP
          INNER JOIN CustomerInformation.PhoneType AS PT ON PT.PhoneTypeID = NP.PhoneTypeID
)
SELECT  NP.phonenumber,
          CTE.phonetype1, 
          CTE.phonetype2
FROM CustomerInformation.NamePhone AS NP
     INNER JOIN CTE on CTE.phonenumber = NP.phonenumber
WHERE DR > 1

In order to get only 1 phone number, then just join on ID as follows:

WITH CTE AS (
     SELECT  NP.ID,
                NP.phonenumber, 
                PT.phonetype1, 
                PT.phonetype2,
                DENSE_RANK() OVER (PARTITION BY NP.phonenumber ORDER BY PT.phonetype1, PT.phonetype2) AS DR
     FROM CustomerInformation.NamePhone AS NP
          INNER JOIN CustomerInformation.PhoneType AS PT ON PT.PhoneTypeID = NP.PhoneTypeID
)
SELECT  NP.phonenumber,
          CTE.phonetype1, 
          CTE.phonetype2
FROM CustomerInformation.NamePhone AS NP
     INNER JOIN CTE on CTE.ID = NP.ID
WHERE DR > 1

This way you will only get one row per duplicate (specifically the last one in alphabetical order by phonetype1 and phonetype2. However if one phone number has many duplicates it will still return everyone except for the first duplicate row.

As for learning advanced T-SQL, the best book I can recommend is T-SQL Querying by Itzik Ben-Gan

  • Thanks for this. It's hard finding advanced SQL problems like this online. I've never used CTE or Over or Partition By. In this particular case I'll probably go with adding in the simple phoneTypeID < phoneTypeID so that it only picks one, but I'll really go over you response to fully understand it for future issues. – Steak514 Dec 1 '19 at 23:33
  • You can easily modify this query to select only 1 of the duplicates (specifically the last one) if you join on ID rather than phone number. I am going to sleep now, will extend my answer tomorrow to show both cases as well as give you some resources to learn advanced T-SQL from. – Chessbrain Dec 1 '19 at 23:36
  • @Steak514 I believe this should give you what you want and I've posted an awesome book to learn T-SQL from. – Chessbrain Dec 2 '19 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.