Select does not return repeated based on a column

Question

Select does not return repeated based on a column

Navigation

#1 by (1 votes)

0

I came across a situation where I have a client registry that can have more than one address in the related tbEndereco table and an address can belong to more than one client. But when I query clients I need to return only 1 of those addresses, whichever one appears first (no criteria). Simplified scheme to follow:

tbCustomer: idClient, Name
tbCliEnd: idClient, idEndereco
tbEndereco: idEndereco, address
tbPrice: id Request, idClient, description

I'm trying:

SELECT c.idCliente, c.Nome, e.endereco, p.descricao
FROM tbPedido p
INNER JOIN tbCliente c 
ON p.idCliente = c.IdCliente
INNER JOIN tbCliEnd ce
ON c.idCliente = ce.idCliente
INNER JOIN tbEndereco e
ON e.idEndereco = ce.idEndereco

But customers with more than one address end up appearing "repeat". They come in each line containing a different address, but under the same name and customer id. How can I do so that only one of the addresses is returned?

sql sql-select sql-server

asked by anonymous 25.07.2018 / 12:31

1 answer

How to generate condition of interruption of a repetition loop in Python during its execution? Set current date in Input type Date

score 1 · Accepted Answer

As the user @ ThiagoMagalhães posted will not work because the alias ce is not recognized within INNER JOIN . Since the ordering does not matter, the solution will be to obtain the address from a MAX :

SELECT      c.idCliente
        ,   c.Nome
        ,   MAX(e.endereco) AS endereco
        ,   p.descricao
FROM        tbPedido    p
INNER JOIN  tbCliente   c   ON p.idCliente  = c.IdCliente
INNER JOIN  tbCliEnd    ce  ON c.idCliente  = ce.idCliente
INNER JOIN  tbEndereco  e   ON e.idEndereco = ce.idEndereco
GROUP BY    c.idCliente
        ,   c.Nome
        ,   p.descricao