How to do the following query without using INNER JOIN?

4

I have table A with the following fields:

ID | ID_PAIS_ENVIO | ID_PAIS_RECIBO
1  | 23            | 47
//...

I have table B with the following fields:

ID | NOME_PAIS
23 | Brasil
47 | Portugal
//...

I need a query that returns the following result:

ID | ID_PAIS_ENVIO | NOME_PAIS_ENVIO | ID_PAIS_RECIBO | NOME_PAIS_RECIBO
1  | 23            | Brasil          | 47             | Portugal

Is it possible to get the above result without doing INNER JOIN 2 times in the same query?

    
asked by anonymous 02.07.2014 / 15:50

6 answers

0

Answering the question: No. You have two different keys and you need a JOIN for each key to fetch the description from the second table.

Any solution other than this will either change the result of your query, or else resolve it out of the database (by the application, generating a collection of countries and assigning the description).

    
02.07.2014 / 16:10
5

Yes, just use different aliases for the table in each JOIN :

SELECT 
    Envios.*,
    PaisEnvio.nome_pais AS nome_pais_envio,
    PaisRecibo.nome_pais AS nome_pais_recibo
FROM Envios
    INNER JOIN Paises AS PaisEnvio
    ON PaisEnvio.id = Envio.id_pais_envio
    INNER JOIN Paises AS PaisRecibo
    ON PaisRecibo.id = Envio.id_pais_recibo 
    
02.07.2014 / 15:56
4

Use the following query:

SELECT PAIS_ENVIO.NOME_PAIS, PAIS_RECIBO.NOME_PAIS
FROM A
INNER JOIN B PAIS_ENVIO on A.ID_PAIS_ENVIO = B.ID
INNER JOIN B PAIS_RECIBO on A.ID_PAIS_RECIBO = B.ID
    
02.07.2014 / 15:56
3
set @entrada = 23;
set @saida = 47;

SELECT 
    e.id_pais as entrada, s.id_pais as saida
FROM
    tbl_pais s,
    tbl_pais e
WHERE (s.id_pais = @saida AND e.id_pais = @entrada);

I would not know another way to do it without some kind of join ... Thanks! : D

    
02.07.2014 / 16:02
2

With Sub Query or Sub Select:

SELECT 
   TabelaA.ID, 
   TabelaA.ID_PAIS_ENVIO, 
   (SELECT TabelaB.NOME_PAIS FROM TabelaB WHERE TabelaB.ID=TabelaA.ID_PAIS_ENVIO) AS NOME_PAIS_ENVIO,
   TabelaA.ID_PAIS_RECIBO,
   (SELECT TabelaB.NOME_PAIS FROM TabelaB WHERE TabelaB.ID=TabelaA.ID_PAIS_RECIBO) AS NOME_PAIS_RECIBO 
FROM TabelaA
The question is without JOIN, but it is recommended to use JOIN because it is faster, since Sub-Select can degrade your search considerably.

Reference:

02.07.2014 / 16:18
0

You can do without join by linking the tables in the WHERE.

   SELECT a.id
     , a.id_pais_envio
     , b_e.nome_pais as nome_pais_envio
     , a.id_pais_recibo
     , b_r.nome_pais as nome_pais_recibo

  FROM tabelaA as a
     , tabelaB as b_e
     , tabelaB as b_r

 WHERE a.id_pais_envio = b_e.id
   AND a.id_pais_recibo = b_r.id
    
28.03.2018 / 19:44