JOIN a table twice for different values in the same query - Mailing list 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                  |
notnull 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    |
timestampwith 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           |
notnull 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