Re: JOIN a table twice for different values in the same query

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