Select case in two tables

Question

I have 4 tables:

Saida{
id int,
idDestino int,
idProduto int,
tipoDestino int,
quant int,
data date,
solicitante varchar}

Paciente{
id int, 
nome varchar}

Unidade{
id int,
nome varchar}

Produto{
id int,
nome varchar,
quant int}

The table saida records items from stock that went out to Patients or Units.

In my program, I do the following: if you leave.tipTill be equal to 0, then the output went to a Patient. if exited.typeDestin is equal to 1, then the output went to a Unit.

So far so good. What I need from you is to make a select +- like this:

SELECT
saida.id, saida.data,
(CASE saida.tipoDestino WHEN 0 THEN paciente.nome WHEN 1 THEN unidade.descricao END) as Destino,
produto.descricao, saida.quant
FROM
saida, paciente, unidade, produto
WHERE
paciente.id = saida.idDestino OR
unidade.id = saida.idDestino

I need you in the field Destino the name of the Paciente or of Unidade, according to field value tipoDestino.

The result even returns values, but shows many repeated fields.

Answer 1

A small modification, sir! Try this untested script and post the result.

SELECT
  s.id,
  s.data,
  case
    when s.tipoDestino = 1 then p.nome
    else u.descricao
  end as Destino,
  pr.descricao,
  s.quant
FROM
  saida s
  left join paciente p on (p.id = s.idDestino)
  left join unidade u on (u.id = s.idDestino)
  left join produto pr on (pr.id = s.idProduto)