How to name different columns with the same reference in PostgreSQL?

Question

I have a routes table that looks like this:

 id | start_point | end_point
----+-------------+-----------
  1 |           1 |         2
  2 |           1 |        10
  3 |           2 |         1
  4 |           2 |         5
  5 |           2 |         9
  9 |           4 |         5
 10 |           5 |         2
 11 |           5 |         4
 12 |           5 |         8
 14 |           7 |         8
...

Both the start_point and end_point refers to a location column from airports table:

Foreign-key constraints:
    "routes_end_point_fkey" FOREIGN KEY (end_point) REFERENCES airports(id) ON DELETE CASCADE
    "routes_start_point_fkey" FOREIGN KEY (start_point) REFERENCES airports(id) ON DELETE CASCADE

airports:

  Column  |         Type          | Collation | Nullable |               Default
----------+-----------------------+-----------+----------+--------------------------------------
 id       | bigint                |           | not null | nextval('airports_id_seq'::regclass)
 location | character varying(20) |           | not null |

Is there a way to create a query that will replace start_point and end_point values on values from location using joins (or any other solution), so result would be something like this:

 id | start_point | end_point
----+-------------+-----------
  1 |    New York |    Moscow
  2 |    New York |     Tokyo
  3 |      Moscow |  New York
  4 |      Moscow |      Rome
...

All I could do by now is to print the results from both start_point and end_point in one column with this query:

select location as departure_point from airports join routes on airports.id = routes.start_point
    union all
select location as destination from airports join routes on airports.id = routes.end_point;

So it looks like this:

 location
----------
 New York 
 New York 
 Moscow   
 Moscow   
...
 Moscow   
 Tokyo    
 New York 
 Rome     
...

Any suggestions?

GMB · Accepted Answer · 2020-02-12 20:39:18Z

4

You can join the airports table twice: once to get the location of the start point, another time to get the location of the end point:

select 
    r.id,
    s.location start_point,
    e.location end_point
from routes r
inner join airports s on s.id = r.start_point
inner join airports e on e.id = r.end_point

answered Feb 12, 2020 at 20:39

GMB

224k25 gold badges102 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to name different columns with the same reference in PostgreSQL?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related