Select case in two tables

Question

Select case in two tables

Navigation

#1 by (3 votes)

1

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 saida table records the inventory items that went out for Patients or Units.

In my program, I do the following: if it does. TypeDestine equals 0, then the output was for a Patient. if destination.Type is equal to 1, then the output went to a Unit.

So far so good. What I need from you is to do 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 the name of the Destino or the Paciente field in the Unidade field, according to the value of the tipoDestino field.

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

mysql select

asked by anonymous 12.02.2017 / 15:34

1 answer

Problems with IF loop IsGenericType, GetGenericTypeDefinition, and Nullable.GetUnderlyingType

score 3 · Accepted Answer

A little modification! 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)