js=# SELECT departure_date, departure.code AS departure_code, arrival.code as
arraival_codeFROM jsjourneys JOIN jsports as departure ON
jsjourneys.departure_port = departure.id JOIN jsports as arrival on
jsjourneys.arraival_port = arraival.id LIMIT4;
Regards,Daniel Hernández.San Diego, CA."The more you learn, more you
earn". --- On Thu 01/10, Colin Wetherbee < [EMAIL PROTECTED] >
wrote:From: Colin Wetherbee [mailto: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Thu,
10 Jan 2008 17:07:00 -0500Subject: [SQL] JOIN a table twice for different
values in the same queryGreetings.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 FROMjsjourneys JOIN jsports ON jsjourneys.departure_port =
jsports.id LIMIT4; 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 | LAXI'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!
