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

 js=# SELECT departure_date, departure.code AS departure_code, arrival.code as arraival_code
FROM jsjourneys 
��� ��� JOIN jsports as departure ON jsjourneys.departure_port = departure.id 
��� �� JOIN jsports as arrival on jsjourneys.arraival_port = arraival.id 
LIMIT
4; 

Regards,

Daniel Hern�ndez.
San Diego, CA.
"The more you learn, more you earn".

 --- On Thu 01/10, Colin Wetherbee < cww@denterprises.org > wrote:
From: Colin Wetherbee [mailto: cww@denterprises.org]
To: pgsql-sql@postgresql.org
Date: Thu, 10 Jan 2008 17:07:00 -0500
Subject: [SQL] JOIN a table twice for different values in the same query

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
Join Excite! - http://www.excite.com
The most personalized portal on the Web!