Query table data with inner join and two foreing keys

Question

I have the following two tables:

<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
}
</style>
</head>
<body>

<table>
  <tr>
    <th>airport</th>
  </tr>
  <tr>
    <td>id_airport</td>
  </tr>
  <tr>
    <td>name_airport</td>
  </tr>
</table>

<h4>and</h4>

</body>
</html>

<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
}
</style>
</head>
<body>

<table>
  <tr>
    <th>route</th>
  </tr>
  <tr>
    <td>id_route</td>
  </tr>
  <tr>
    <td>id_airport_origin</td>
  </tr>
  <tr>
    <td>id_airport_destination</td>
  </tr>
</table>

</body>
</html>

route.id_airport_origin and route.id_airport_destination are both foreign keys of airport.id_airport

I need to query the name_airport column that matches with its foreing keys.

I have tried to execute the following query with no success.

SELECT route.id.route, airport.name_airport, airport.name_airport
FROM route
INNER JOIN airport ON route.id_airport_origin AND route.id_airport.destination = airport.id_airport

I have tried several combinations of the above code but couldn't get the right query.

Any ideas?

This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. PS For SQL use a code block, snippets are for css/html/javascript. — philipxy, May 23 '20 at 02:16
This is a self-join. [How to get matching data from another SQL table for two different columns: Inner Join and/or Union?](https://stackoverflow.com/q/27682228/3404097) PS Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] — philipxy, May 23 '20 at 02:20

score 1 · Accepted Answer · answered May 23 '20 at 01:16

Your query returs no rows because you are looking for an airport whose id is equal to both id_airport_origin and id_airport_destination of the route: obviously this cannot match (... unless the origin and destination of the route are the same).

Insead, you need to join the airport table twice, once for the origin and another for the destination:

select
    r.id_route,
    ao.name_airport name_airport_origin
    ad.name_airport name_airport_destination
from route r
inner join airport ao on ao.id_airport = r.id_airport_origin
inner join airport ad on ad.id_airport = r.id_airport_destination

your correction made the trick, I was making mistakes with the inner join clause, my confusion came from the double inner join with the same alias. — aldehc99, May 23 '20 at 20:54

Query table data with inner join and two foreing keys

1 Answers1