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_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 Hernndez.San Diego, CA."The more you learn, more you earn". --- On Thu 01/10, Colin Wetherbee < cww(at)denterprises(dot)org > wrote:From: Colin Wetherbee [mailto: cww(at)denterprises(dot)org]To: pgsql-sql(at)postgresql(dot)orgDate: 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!