How to do the following query without using INNER JOIN?

Asked

Viewed 1,555 times

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

You can get the above result without doing INNER JOIN 2 times in the same query?

mysql join
  • 2

    What’s wrong with doing two Joins?

6 answers

5

Yes, just use it aliases different 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

  • Thanks, but look, this is what I wanted to avoid, at the end of the post I ask if it is possible without doing INNER JOIN 2 times, a single query that returns 2 lines in only 1.

  • Oops, I didn’t get that part. It is even possible to do a single JOIN, but in this case you will not be able to distinguish the sending and receiving countries by SQL itself. And you will have two lines for each upload in the result.

  • In terms of performance, there would be some considerable impact?

  • I don’t think so, Filipe. But only testing to say for sure.

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

  • Thanks, but look, this is what I wanted to avoid, at the end of the post I ask if it is possible without doing INNER JOIN 2 times.

  • 1

    I got it. I hit my eye on the title and understood that you needed INNER JOIN. You can try with Subqueries.

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 wouldn’t know any other way to do it without some kind of... Valeuu! D

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

Obs: The question is without JOIN, but it is recommended to use JOIN because it is faster, since Sub-Select can considerably degrade your search.

Reference:

0

You can do it without John connecting the tables in 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

0


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

Any solution other than this will alter the outcome of your query, or else resolve outside the database (by applying, generating a collection of countries and assigning the description).

Browser other questions tagged mysql join

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