Re: JOIN a table twice for different values in the same query - Mailing list pgsql-sql

Colin Wetherbee wrote:
> Greetings.
> 
> I have two tables I'm having a little trouble figuring out how to JOIN.
> 
> One contains a list of airports along with their IATA codes, cities, 
> names, and so forth.  This table also contains an id column, which is a 
> serial primary key.
> 
> The other table contains a list of flights, each of which has a 
> departure_port and an arrival_port, which are foreign keys referencing 
> the id field of the first table.
> 
> I would like to construct a query on the flight table that returns the 
> names of both the departure port and the arrival port.
> 
> The following query shows how I would get just the departure port.
> 
> js=# SELECT departure_date, jsports.code AS departure_code FROM
> jsjourneys JOIN jsports ON jsjourneys.departure_port = jsports.id LIMIT
> 4;
> 
>  departure_date | departure_code
> ----------------+----------------
>  2006-11-19     | ATL
>  2006-11-16     | ATL
>  2006-11-19     | BHM
>  2007-02-03     | BOS
> (4 rows)
> 
> When I SELECT jsports.code, the result comes from the JOIN ... ON 
> jsjourneys.departure_port = jsports.id.
> 
> I would *also* like to include something in the query to get the 
> jsports.code for jsjourneys.arrival_port, but I'm unsure how to do this, 
> since SELECTing jsports.code twice would be ambiguous (and, in any case, 
> just duplicates the departure_code).
> 
> I'd like to produce a result set that looks something like the following 
> (which doesn't come from a real query).
> 
>  departure_date | departure_code | arrival_code
> ----------------+----------------+--------------
>  2006-11-19     | ATL            | JFK
>  2006-11-16     | ATL            | DFW
>  2006-11-19     | BHM            | IAH
>  2007-02-03     | BOS            | LAX
> 
> I'd appreciate some help.
> 
> FYI, table definitions for jsjourneys and jsports follow.
> 
> js=# \d jsjourneys
>                                         Table "public.jsjourneys"
>        Column        |           Type           |   Modifiers
> ---------------------+--------------------------+--------------------------------------------------------- 
> 
>  id                  | bigint                   | not null default 
> nextval('jsjourneys_id_seq'::regclass)
>  userid              | bigint                   | not null
>  typeid              | integer                  | not null
>  carrier             | integer                  |
>  number              | integer                  |
>  departure_port      | integer                  | not null
>  arrival_port        | integer                  | not null
>  departure_gate      | character varying        |
>  arrival_gate        | character varying        |
>  departure_date      | date                     | not null
>  fare_class          | integer                  |
>  scheduled_departure | timestamp with time zone |
>  scheduled_arrival   | timestamp with time zone |
>  actual_departure    | timestamp with time zone |
>  actual_arrival      | timestamp with time zone |
>  equipment           | integer                  |
>  notes               | character varying(1500)  |
>  seat                | character varying(4)     |
>  confirmation        | character varying(20)    |
> Indexes:
>     "jsjourneys_pkey" PRIMARY KEY, btree (id)
> Foreign-key constraints:
>     "jsjourneys_arrival_port_fkey" FOREIGN KEY (arrival_port) REFERENCES 
> jsports(id)
>     "jsjourneys_carrier_fkey" FOREIGN KEY (carrier) REFERENCES 
> jscarriers(id)
>     "jsjourneys_departure_port_fkey" FOREIGN KEY (departure_port) 
> REFERENCES jsports(id)
>     "jsjourneys_equipment_fkey" FOREIGN KEY (equipment) REFERENCES 
> jsequipment(id)
>     "jsjourneys_fare_class_fkey" FOREIGN KEY (fare_class) REFERENCES 
> jsfareclasses(id)
>     "jsjourneys_typeid_fkey" FOREIGN KEY (typeid) REFERENCES 
> jsjourneytypes(id)
>     "jsjourneys_userid_fkey" FOREIGN KEY (userid) REFERENCES jsusers(id)
> 
> js=# \d jsports
>                                 Table "public.jsports"
>   Column   |       Type        |                      Modifiers
> -----------+-------------------+------------------------------------------------------ 
> 
>  id        | integer           | not null default 
> nextval('jsports_id_seq'::regclass)
>  code      | character varying | not null
>  city      | character varying | not null
>  full_city | character varying | not null
>  name      | character varying |
> Indexes:
>     "jsports_pkey" PRIMARY KEY, btree (id)
>     "jsports_index_city" btree (city)
>     "jsports_index_code" btree (code)
> 
> Thanks!
> 
> Colin
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>               http://archives.postgresql.org
> 

Try joining twice, something like:

SELECT departure_date,       dp.code AS departure_code,       ap.code AS arrival_code
FROM jsjourneys
JOIN jsports dp ON jsjourneys.departure_port = jsports.id
JOIN jsports ap ON jsjourneys.arrival_port=jsports.id