Select do not return repeated based on a column

Asked

Viewed 54 times

0

I came across a situation where I have a register of customers who may have more than one address in the related tbEndereco table and an address may belong to more than one customer. But when consulting clients I need to return only 1 of these addresses, being the one that appears first (there is no discretion). Simplified scheme below:

  • tbCliente : idCliente, Name
  • tbCliEnd: idCliente, idEndereco
  • tbEndereco: idEndereco, address
  • tbPedido: idPedido, idCliente, Descricao

I’m trying to:

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

However customers who have more than one address end up appearing "repeated". They come containing in each line a different address, but under the same name and customer id. How can I get only one address returned?

sql sql-server select-sql

  • puts your example at http://sqlfiddle.com/ makes it easier to help you

  • Alamo, in tbPedido does not have a field referring to idEndereco in tbEndereco? How will you know which address to deliver if there are more than 1 registered in the customer? In my view it is a failure. If there is no reference, then how will you only search for 1 address if you don’t know which one to search for!? When filtering the address when adding the request, then yes you use the table tbCliEnd to filter which addresses are allowed for such a client.

  • @RBZ Unfortunately there is no reference to the address in the tabPedido. This is an old bank, which serves other systems and can not modify it. Anyway this consultation is not to feed deliveries, just a report. I just need that.

  • use the Distinct could help here?

  • @Alamo What you could do in this case is to add a field, for example: principal, and allow you to always only have 1 main customer address. This would solve a lot of things.

1 answer

1


As the user @Thiagomagalhães posted will not work because the alias ce is not recognized within the INNER JOIN.

Since ordering does not matter, the solution will be to get 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

Browser other questions tagged sql sql-server select-sql

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