Message-ID: <47869704.8050306@denterprises.org>
Date: Thu, 10 Jan 2008 17:07:00 -0500
From: Colin Wetherbee <cww@denterprises.org>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: JOIN a table twice for different values in the same query
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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