INNER JOIN with two fields of the same table

Question

I’m having a problem I haven’t found a solution to so far. I have 3 tables in Mysql:

• Units:containing information from "stores".
• Orders: contain the order information made by loja01 for loja02, both registered in the units table.
• Users: and finally users with information of who made the request.

The problem is when I try to recover the source and destination information that are both in the drives table.

SELECT
ped.cod_pedido,
ped.origem,
ped.destino,
ped.obs,
ped.usuario,
und.nome_unidade
FROM pedidos as ped
INNER JOIN usuarios as usr ON (ped.usuario = usr.cod_user)
INNER JOIN unidades as und ON (ped.origem = und.cod_unidades)

But by doing so I cannot recover the information I need only returns these results:

I’d like to make the names of the units appear instead of your code.

Could someone help me?

Answer 1

I believe you can do something like that, but I have no way to test it here:

SELECT
ped.cod_pedido,
und_origem.nome as Origem
und_destino.nome as Destino
ped.obs,
ped.usuario,
FROM pedidos as ped
INNER JOIN usuarios as usr ON (ped.usuario = usr.cod_user)
INNER JOIN unidades as und_origem ON (ped.origem = und.cod_unidades)
INNER JOIN unidades as und_destino ON (ped.destino = und.cod_unidades)

Answer 2

You need to do 2 joins, one for source and one for destination.

SELECT
    un_src.nome_unidade as origem,
    un_dst.nome_unidade as destino,
FROM
    pedidos
INNER JOIN unidades as un_src ON un_src.cod_unidade = pedidos.origem
INNER JOIN unidades as un_dst ON un_dst.cod_unidade = pedidos.destino