I have a table Clientes and a ClientesTel . I need to present a list where Código do Cliente, Nome, Telefone e o email appears, but by structure I can have several phones. Here's 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 not do this filter. It always shows folded.
I'm using SQL Server 2014 and ASP.Net.
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 is sqlfiddle.com is the expected return right?
John has 2 (two) phones however you need only one return, right? In case the return is always the Customer's first phone.
PARTITION BY: This function is to count how many occurrences of a given data happened in a field, in the case I used the Name, so I am counting how many occurrences had the same name, in the case of john will have occurrence 1 and 2 for the field "number", logo after I make a select to get all the data that have the field "number" = 1 that will be all Customers that have at least 1 phone thus ignoring the subsequent occurrences. I hope the explanation is clear.