[FIXED] How to replace the originStation and destinationStation in Journey Table with the station-id from a different table i,e Station Table in Postgres

Issue

There are two table "Journey" and "Station". Journey table consist of journey-id, originStation and destinationStation. Station table consist of station-id, stationName.

Journey table

id	originStation	destinationStation
1	Delhi	Mumbai

Station Table

station_id	stationName
a1	Delhi
a2	Mumbai

Resulting table must be following using SQL. Thank you for helping me out.

id	originStation	destinationStation
1	a1	a2

Tried the following Query but it replaces both destinationStation and originStation with same station_id

BEGIN TRANSACTION;
/* Create Table Journey */
CREATE TABLE journey(id integer PRIMARY KEY, originStation text,destinationStation text);
INSERT INTO journey VALUES(1,'Delhi', 'Mumbai');

/* Create Table Station */

CREATE TABLE station(station_id text PRIMARY KEY, stationName text);
INSERT INTO station VALUES('a1','Delhi');
INSERT INTO station VALUES('a2','Mumbai');

COMMIT;
/* Query*/
SELECT journey.id, station.station_id as originStation, station.station_id as destinationStation
        FROM journey  JOIN station
          ON journey.destinationstation = station.stationName;

Solution

You would just join twice:

select j.*, so.stationId as originStationId, sd.stationId as destinationStationId
from journey j left join
     stations so
     on j.originStation = so.stationName left join
     stations sd
     on j.destinationStation = sd.stationName;

Answered By - Gordon Linoff

Answer Checked By - Marilyn (PHPFixing Volunteer)