Show only one record of each ID

Asked

Viewed 1,121 times

1

I have a table Clientes and a ClientesTel. I need to present a list where the Código do Cliente, Nome, Telefone e o email, but by the structure I can have several phones. Follow the SQL I’m trying to modify:

SELECT ClientesTel.id, Clientes.nome, ClientesTel.telefone, ClientesTel.email
    FROM ClientesTel INNER JOIN Clientes ON ClientesTel.id = Clientes.id

But I can’t make this filter. It always shows folded.

I am using SQL Server 2014 and ASP.Net.

sql asp.net sql-server

  • What is folded? Show how your data is, the result obtained and what is expected. For me there is nothing duplicated: http://sqlfiddle.com/#! 6/07114/1 Although the correct one would be another query: http://sqlfiddle.com/#! 6/07114/2. There’s a customer bent so both phones can appear. Is this wrong? You shouldn’t be.

  • (15) 99135-9999 [email protected] 3 Albert Brum (15) 99173-9999 [email protected] 1 Fabio Zanardo (15) 99695-9999 1 Fabio Zanardo

  • This doesn’t help anything. I don’t see anything duplicated there. But if you think you are, explain it better. Did you see what I put together? If you cannot clarify the problem the question will have to be closed.

  • So.. let me see if I can pass on what I need... In his second example, appears John 2 times because he has 2 registered phones... In a list of Customers with phone, you would count as if you had 4 registered customers... and not 3 as it really is... if you follow the logic, if a customer has 5 or 6 phones, you will end up having a duplicate listing 6 times with the same customer... I don’t know if I can be clear in this explanation... it’s as if I took each customer and showed only 1 phone of each customer.

  • I quit, I showed you there’s no duplication, you can’t do it show duplication, I can’t help it. I showed the two forms, one that only shows one phone per customer and the other that shows all headphones, then duplicates, but was asked to p/ list all headphones, it is physically impossible to show 2 phones from the same client without repeating except change the structure of the database.

  • So... in the first consultation, in my view John has 2 phones, am I right? If yes, the second phone of John, appears to Joseph... there is a mixture of the Ids...

  • No, I don’t know where you’re seeing it.

  • See if it’s easier to identify now... http://sqlfiddle.com/#! 6/b6704/1

  • No, there you are manually duplicating the information. In mine there are 2 different information, in yours there are two equal information. If the data is wrong, there is no code that works. And this query which you are using is wrong, the other is right. I said it in the 1st. comment. This query compares banana with orange. And this makes no sense. This is correct http://sqlfiddle.com/#! 6/07114/2

  • So. if you notice the first SQL string that you sent me (http://sqlfiddle.com/#!6/07114/1) and mine (http://sqlfiddle.com/#!6/b6704/1) I just changed the information of the fields to show that there is an error in the first sql q vc sent me... But it’s okay I’ll figure it out here... thanks for the help..

Show 5 more comments

1 answer

1


I understood your question and the select for your answer is this, correct?

WITH  Tels as(
SELECT  ClientesTel.id, 
   ROW_NUMBER() OVER 
   (PARTITION BY Clientes.nome ORDER BY ClientesTel.id)
  as number,
  Clientes.nome, ClientesTel.telefone, ClientesTel.email
    FROM Clientes
    INNER JOIN ClientesTel
    ON Clientes.id = ClientesTel.Clientes_id)
 select id, nome, telefone, email from tels
 where number = 1

See how the result looks sqlfiddle.com is the right expected return?

João has 2(two) phones but you need only one return, correct? In case the return is always the customer’s first phone.

PARTITION BY: This function is used to count how many occurrences of a given data happened in a field, in this case I used the Name, so I am counting how many occurrences had the same name, in the case of John will occur 1 and 2 for the field "number", right after I make a select to take all the data that are with the field "number" = 1 that will be all Customers that have at least 1 phone ignoring the subsequent occurrences. I hope the explanation is clear.

Browser other questions tagged sql asp.net sql-server

You are not signed in. Login or sign up in order to post.