Re: JOIN a table twice for different values in the same query
От | Paul Lambert |
---|---|
Тема | Re: JOIN a table twice for different values in the same query |
Дата | |
Msg-id | 478699D0.7070608@optusnet.com.au обсуждение исходный текст |
Ответ на | JOIN a table twice for different values in the same query (Colin Wetherbee <cww@denterprises.org>) |
Ответы |
Re: JOIN a table twice for different values in the same query
(Colin Wetherbee <cww@denterprises.org>)
|
Список | pgsql-sql |
Colin Wetherbee wrote: > 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 > Try joining twice, something like: SELECT departure_date, dp.code AS departure_code, ap.code AS arrival_code FROM jsjourneys JOIN jsports dp ON jsjourneys.departure_port = jsports.id JOIN jsports ap ON jsjourneys.arrival_port=jsports.id
В списке pgsql-sql по дате отправления:
Предыдущее
От: Colin WetherbeeДата:
Сообщение: JOIN a table twice for different values in the same query
Следующее
От: "Daniel Hernandez"Дата:
Сообщение: Re: JOIN a table twice for different values in the same query