INNER JOIN with two fields from the same table

Navigation
7

I'm having a problem that I have not found a solution to date. I have 3 tables in MySQL:

  • Units: containing store information.
  • Orders: Contains the order information made by store01 for store02, both of which are listed in the units table.
  • Users: and lastly users with information of who placed the request.


TheproblemiswhenItrytoretrievethesourceanddestinationinformationthatarebothinthedrivestable.

SELECTped.cod_pedido,ped.origem,ped.destino,ped.obs,ped.usuario,und.nome_unidadeFROMpedidosaspedINNERJOINusuariosasusrON(ped.usuario=usr.cod_user)INNERJOINunidadesasundON(ped.origem=und.cod_unidades)

ButinthiswayIcannotretrievetheinformationIneedjustreturnstheseresults:

I would like to make the drive names appear instead of your code.

Could anyone help me?

    
asked by anonymous 29.01.2015 / 16:04

2 answers

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
    
29.01.2015 / 16:19
2

I think you can do something like this, but I can not test 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)
    
29.01.2015 / 16:20
SQLServer Portuguese Error Messages - (LocalDB) \ v11.0 How does FireMonkey (Delphi) compile for Android?