CODEWITHSUNDEEP
sql-serversql-server-2008sql
Guilherme Longo
Guilherme Longo

Reputation: 655

Pull name from one table unless row exists in another table

I have the following query:

SELECT a.id, a.Nome, ...
FROM cadClientes as a 
...
join Contatos_Clientes as f on a.id = f.idCliente

The problem is that sometimes there isn't a row in Contatos_Clientes.

I want to return Nome from Contatos_Clientes if it exists, otherwise return Nome from cadClientes.

Upvotes: 2

Views: 69

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

If you want to show values from one table only when they don't exist in another, you should change the join to a LEFT OUTER JOIN. Example:

SELECT Nome = COALESCE(c.Nome, cc.Nome) -- ,... Other columns
FROM dbo.cadClientes AS c
-- other joins...
LEFT OUTER JOIN dbo.Contatos_Clientes AS cc
ON c.id = cc.idCliente;

Please always use the schema prefix when creating / referencing objects, and try to use meaningful aliases instead of a,b,c...

Upvotes: 6

Related Questions