JOIN with two columns in the same table

Asked

Viewed 2,492 times

6

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

On the table quotation, has the column with cpf_cnpj from warhead and fate.

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'

Besides the data of the quotation table, I need to get the data of the origin and the destination.

That’s possible?

php mysql mysqli
  • 1

    You can put a sample of the table contents?

2 answers

3


You came pretty close, I believe your mistake 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 get only the social reasons of each customer would be so:

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, also used a alias in the columns getting rsocial_origem and rsocial_destino. So in your PHP you take 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 take only the data of the source client just use cliente_origem.*

  • It shows all table data cotacao, clienteOrigem , but shows no table value clienteDestino.

  • @Tiago Have you checked the integrity of your data? Really the value of cotacao.destino exists in the clients table?

  • Yes, there are http://i.imgur.com/Icozvqd.png

  • @Tiago Como está salvo na coluna destino da tabela cotação? Está sem pontuação?

  • Miss my...rsrs, the field is destinatario and not target. (http://i.imgur.com/j2VDCSd.png). Now showing the values, but I have a question... How to distinguish the source and destination value, the fields being the same?

  • They’re not the same, as @ctgPi explained, when you use alias you create a "new table". If you use cliente_destino it will only return data from your relationship cliente_destino.cpf_cnpj = cotacao.destinatario

  • Great! How would you show in PHP the echo of clienteOrigem.rsocial and clienteDestino.rsocial?

  • @Tiago I updated my answer with an example

  • You’re putting 2 selects?

  • The primeiro select from my answer is an example, the segundo is showing how you would get social reasons both made with 2 LEFT JOIN

  • I edited my initial code.

  • I’d have to do alias for all fields?

  • @Tiago Exatamente

  • I updated my post. It’s giving an error, Unknown column 'clienteDestino.ID_Cliente' in 'field list'

  • @James You wrote wrong on LEFT JOIN clientes clienteDdestino is with two D’s, that’s right LEFT JOIN clientes clienteDestino

  • How do I include another table config?

  • Let’s go continue this discussion in chat.

  • Discover :), Thank you.

Show 13 more comments

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'

The <tabela> AS <novo_nome> renames the tables, and creates a new virtual table, allowing you to do several JOINs (including INNER JOINs, if applicable) with the same table, but in different contexts.

The catch is that renaming the table nay renames table columns; if you are pulling the result as a array associative, you may encounter some error or miss some columns. You fix this by doing

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 …

  • You explained it better than I did :)

  • It shows all table data cotacao, clienteOrigem , but shows no table value clienteDestino.

  • 1

    @Tiago, just complementing you can also search all the fields of a table cliente_origem.* and then rename only the fields from the second table cliente_destino.nome AS cliente_destino_nome. Then the field $row['nome'] in his array will refer to the table "origin" and the referring to destination will be renamed $row['cliente_destino_nome'].

  • Yes, and I personally am in favour of cliente_origem.nome AS \cliente_origin.name``, etc. but I understand that most people don’t like to get away from the dentifiers in the consultation itself.

Browser other questions tagged php mysql mysqli

You are not signed in. Login or sign up in order to post.