JOIN a table twice for different values in the same query

From: Colin Wetherbee <cww(at)denterprises(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: JOIN a table twice for different values in the same query
Date: 2008-01-10 22:07:00
Message-ID: 47869704.8050306@denterprises.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Paul Lambert 2008-01-10 22:18:56 Re: JOIN a table twice for different values in the same query
Previous Message Chris Browne 2008-01-10 20:37:54 Re: trigger for TRUNCATE?