JOIN with two columns in the same table

Navigation
6

Good morning. How do I make a JOIN, two column in the same table?

In the quote table, you have the column with the ogirem and destination cpf_cnpj. 

SELECT 
    cotacao.*,
    clienteOrigem.ID_Cliente    as origemIdcliente,
    clienteOrigem.cpf_cnpj      as origemCpfcnpj,
    clienteOrigem.isento        as origemIsento,
    clienteOrigem.suframa       as origemSuframa,
    clienteOrigem.rsocial       as origemRsocial,
    clienteOrigem.nfantasia     as origemNfantasia,
    clienteOrigem.ie            as origemIe,
    clienteOrigem.im            as origemIm,
    clienteOrigem.cep           as origemCep,
    clienteOrigem.rua           as origemRua,
    clienteOrigem.num           as origemNum,
    clienteOrigem.comple        as origemComple,
    clienteOrigem.bairro        as origemBairro,
    clienteOrigem.cidade        as origemCidade,
    clienteOrigem.codMunicipio  as origemCodMunicipio,
    clienteOrigem.estado        as origemEstado,
    clienteOrigem.pais          as origemPais,
    clienteOrigem.email         as origemEmail,

    clienteDestino.ID_Cliente   as destinoIdcliente,
    clienteDestino.cpf_cnpj     as destinoCpfcnpj,
    clienteDestino.isento       as destinoIsento,
    clienteDestino.suframa      as destinoSuframa,
    clienteDestino.rsocial      as destinoRsocial,
    clienteDestino.nfantasia    as destinoNfantasia,
    clienteDestino.ie           as destinoIe,
    clienteDestino.im           as destinoIm,
    clienteDestino.cep          as destinoCep,
    clienteDestino.rua          as destinoRua,
    clienteDestino.num          as destinoNum,
    clienteDestino.comple       as destinoComple,
    clienteDestino.bairro       as destinoBairro,
    clienteDestino.cidade       as destinoCidade,
    clienteDestino.codMunicipio as destinoCodMunicipio,
    clienteDestino.estado       as destinoEstado,
    clienteDestino.pais         as destinoPais,
    clienteDestino.email        as destinoEmail


FROM cotacao
    LEFT JOIN clientes clienteOrigem on clienteOrigem.cpf_cnpj = cotacao.origem
    LEFT JOIN clientes clienteDdestino on clienteDdestino.cpf_cnpj = cotacao.destinatario

WHERE 
    ID_Cotacao = '3'

In addition to the quote table data, I need to get the source and target data.

Is this possible?

    
asked by anonymous 16.06.2015 / 17:23

2 answers

3

You have come very close, I believe your error is in the absence of a alias 

SELECT * from cotacao
LEFT JOIN clientes cliente_origem on cliente_origem.cpf_cnpj = cotacao.origem
LEFT JOIN clientes cliente_destino on cliente_destino.cpf_cnpj = cotacao.destinatario
WHERE ID_Cotacao = '3'

If you want to take only the social reasons of each client would be like this: 

SELECT cliente_origem.rsocial as rsocial_origem,  
       cliente_destino.rsocial as rsocial_destino, 
from cotacao
LEFT JOIN clientes cliente_origem on cliente_origem.cpf_cnpj = cotacao.origem
LEFT JOIN clientes cliente_destino on cliente_destino.cpf_cnpj = cotacao.destinatario
WHERE ID_Cotacao = '3'

Pay attention to the difference between cliente_origem and cliente_destino , I also used alias in the columns getting rsocial_origem and rsocial_destino . So in your PHP you get it this way: 

echo $row['rsocial_origem']; /* razão do cliente de origem */
echo $row['rsocial_destino']; /* razão do cliente de destino */

If you want to get only the data from the source client, just use cliente_origem.*

    
16.06.2015 / 17:37
3
SELECT * from cotacao
LEFT JOIN clientes AS cliente_origem ON cliente_origem.cpf_cnpj = cotacao.origem
LEFT JOIN clientes AS cliente_destino ON cliente_destino.cpf_cnpj = cotacao.destino
WHERE ID_Cotacao = '3'

<tabela> AS <novo_nome> renames the tables, and creates a new virtual table, allowing you to make multiple JOIN s (including INNER JOIN s, if any) with the same table, but in different contexts. / p>

The catch is that renaming the no table renames the columns of the table; if you are pulling the result as an associative array , you may encounter some error or feel the lack of some columns. You fix this by 

SELECT
    cliente_origem.nome AS cliente_origem_nome,
    cliente_origem.endereco AS cliente_origem_endereco,
    -- outras colunas de cliente_origem…
    cliente_destino.nome AS cliente_destino_nome,
    cliente_destino.endereco AS cliente_destino_endereco,
    -- outras colunas de cliente_destino…
    -- outras colunas de outras tabelas…
FROM …
    
16.06.2015 / 17:38
How to find CSS images and styles that are not used on a website? Is there any risk of submitting form with HTML?