CODEWITHSUNDEEP
sqldb2
Graffiti908
Graffiti908

Reputation: 63

How to distinct duplicate rows(by field) in inner join clause

I have two tables: 

Customers -> CustomerId, Name 

Phones -> PhoneId , CustomerId , Phone

This tables are join through CustomerId field. Every customer have more than one phone in phones table. I need to get only one of them. How can i do this. 

SELECT 
       Customers.CustomerId , 
       Customers.Name ,
       Phones.Phone
FROM Customers 
     INNER JOIN Phones ON Customers.CustomerId = Phones.CustomerId

In this case i get something like:

+----------+----+-----------+
|CustomerId|Name|Phone      |
+----------+----+-----------+
|1010      |Jack|999-999-999|
+----------+----+-----------+
|1010      |Jack|888-888-888|
+----------+----+-----------+
|1010      |Jack|111-111-111|
+----------+----+-----------+
|2020      |Pit |123-123-123|
+----------+----+-----------+
|2020      |Pit |321-321-321|
+----------+----+-----------+

But i need only one customer and one phone number like:

+----------+----+-----------+
|CustomerId|Name|Phone      |
+----------+----+-----------+
|1010      |Jack|888-888-888|
+----------+----+-----------+
|2020      |Pit |123-123-123|
+----------+----+-----------+

I tried to do it using subquery:

   SELECT 
   c.CustomerId , 
   c.Name ,
   SELECT ( p.Phone FROM Phones WHERE p.CustomeId=c.CustomerId FETCH FIRST 1 ROWS ONLY)
   FROM Customers c

But it works VERYYYY SLLOOOW thats why i cant use subquery. How can i do it anyway else?

Upvotes: 1

Views: 245

Answers (4)

Siva
Siva

Reputation: 9101

Try this

Note: This is not tested code

SELECT 
       distinct Phones.Phone,
       Customers.CustomerId , 
       Customers.Name 

FROM Customers 
     INNER JOIN Phones ON Customers.CustomerId = Phones.CustomerId

Upvotes: 0

Jade
Jade

Reputation: 2992

this will returns one phone number per customer id

SELECT 
    c.CustomerId, 
    c.Name,
    Min(p.Phone) Phone -- Or Max(p.Phone)
FROM Customers c
    LET OUTER JOIN Phones p on c.CustomeId = p.CustomeId
GROUP BY c.CustomerId, c.Name

Upvotes: 0

WarrenT
WarrenT

Reputation: 4532

The Row_Number() function will help you out.

WITH X AS
(SELECT C.CustomerId
      , C.Name
      , P.Phone
      , ROW_NUMBER() OVER (PARTITION BY P.CustomerID) AS Pick
  FROM Customers C
  JOIN Phones    P   ON C.CustomerId = P.CustomerId 
)
SELECT CustomerID
     , Name
     , Phone
  FROM X
  WHERE Pick = 1

This will assign a cardinal number to each phone number pertaining to a given customer, and choose only the first one. Now in this case, we are arbitrarily picking which phone number is the first one.

Suppose you had some other field in your Phones table that might help you decide which is the best to choose. Let's say you have a Last_Used timestamp, and the best number to pick can be the one used most recently. Then you can use an expression like this:

ROW_NUMBER() OVER (PARTITION BY P.CustomerID  ORDER BY P.Last_Used desc)

Of course, if you were talking about some other type of field, it might possibly make sense to simply use MIN() or MAX(), and GROUP BY CustomerID. But I can't see any rationale to do so with phone numbers.

Upvotes: 1

Biby Augustine
Biby Augustine

Reputation: 425

best option is to use pivot . for more information visit http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ here. also by using this you are able to view all of your telephone numbers too....

Upvotes: 0

Related Questions