Getting two different results in a SQL SELECT with the same variable-CodePudding

I'm trying to get two different result sets with a SELECT using an INNER JOIN on different tables.

Let me show you what I got so far:

select 
    refugo.id, 
    cadastro_motivos.motivo as defeito, 
    cadastro_motivos.motivo as causa, 
    refugo.op_producao, 
    refugo.quantidade, 
    maquina.nome,
    to_char(refugo.data, 'DD/MM/YY HH24:MI:SS')
from 
    refugo 
inner join 
    cadastro_motivos on refugo.id_motivo = cadastro_motivos.id
inner join 
    maquina on maquina.id = refugo.id_maquina
order by 
    refugo.id asc

I've been trying to use an alias for the column cadastro_motivos.motivo, but I don't have no clue how to connect with the inner join that I use.

The column refugo.id_motivo is the one I need to use for make a connection. I have a second column refugo.id_motivo2 that I need to refer to the column cadastro_motivos.id.

This is a valid question? I am lost in my thoughts here... Any advice will be great!

CodePudding user response：

Join the same table cadasto_motivo twice. You'll need to use aliases (m1 and m2 below) to differentiate the columns of them.

For example:

select 
    refugo.id, 
    m1.motivo as defeito, -- retrieving "motivo" from 1st one
    m2.motivo as causa, -- retrieving "motivo" from 2nd one
    refugo.op_producao, 
    refugo.quantidade, 
    maquina.nome,
    to_char(refugo.data, 'DD/MM/YY HH24:MI:SS')
from 
    refugo 
inner join -- joining first time as "m1"
    cadastro_motivos m1 on refugo.id_motivo = m1.id
inner join -- joining second time as "m2"
    cadastro_motivos m2 on refugo.id_motivo2 = m2.id
inner join 
    maquina on maquina.id = refugo.id_maquina
order by 
    refugo.id asc