CODEWITHSUNDEEP
sqljoinselectforeign-keysinner-join
aldehc99
aldehc99

Reputation: 23

Query table data with inner join and two foreing keys

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?

Upvotes: 1

Views: 40

Answers (1)

GMB
GMB

Reputation: 222402

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

Upvotes: 1

Related Questions