Re: JOIN a table twice for different values in the same query - Mailing list pgsql-sql

js=# SELECT departure_date, departure.code AS departure_code, arrival.code as arraival_code<br />FROM jsjourneys <br
/>������ JOIN jsports as departure ON jsjourneys.departure_port = departure.id <br />��� �� JOIN jsports as arrival on
jsjourneys.arraival_port= arraival.id <br />LIMIT<br />4; <br /><br />Regards,<br /><br />Daniel Hern�ndez.<br />San
Diego,CA.<br />"The more you learn, more you earn".<br /><br /> --- On Thu 01/10, Colin Wetherbee <
cww@denterprises.org> wrote:<br /><blockquote style="border-left: 2px solid orange; padding-left: 7px; margin-left:
7px;"><b>From:</b>Colin Wetherbee [mailto: cww@denterprises.org]<br /><b>To: </b>pgsql-sql@postgresql.org<br /><b>Date:
</b>Thu,10 Jan 2008 17:07:00 -0500<br /><b>Subject: </b>[SQL] JOIN a table twice for different values in the same
query<br/><br />Greetings.<br /><br />I have two tables I'm having a little trouble figuring out how to JOIN.<br /><br
/>Onecontains a list of airports along with their IATA codes, cities, <br />names, and so forth. This table also
containsan id column, which is a <br />serial primary key.<br /><br />The other table contains a list of flights, each
ofwhich has a <br />departure_port and an arrival_port, which are foreign keys referencing <br />the id field of the
firsttable.<br /><br />I would like to construct a query on the flight table that returns the <br />names of both the
departureport and the arrival port.<br /><br />The following query shows how I would get just the departure port.<br
/><br/>js=# SELECT departure_date, jsports.code AS departure_code FROM<br />jsjourneys JOIN jsports ON
jsjourneys.departure_port= jsports.id LIMIT<br />4;<br /><br /> departure_date | departure_code<br
/>----------------+----------------<br/> 2006-11-19 | ATL<br /> 2006-11-16 | ATL<br /> 2006-11-19 | BHM<br />
2007-02-03| BOS<br />(4 rows)<br /><br />When I SELECT jsports.code, the result comes from the JOIN ... ON <br
/>jsjourneys.departure_port= jsports.id.<br /><br />I would *also* like to include something in the query to get the
<br/>jsports.code for jsjourneys.arrival_port, but I'm unsure how to do this, <br />since SELECTing jsports.code twice
wouldbe ambiguous (and, in any case, <br />just duplicates the departure_code).<br /><br />I'd like to produce a result
setthat looks something like the following <br />(which doesn't come from a real query).<br /><br /> departure_date |
departure_code| arrival_code<br />----------------+----------------+--------------<br /> 2006-11-19 | ATL | JFK<br />
2006-11-16| ATL | DFW<br /> 2006-11-19 | BHM | IAH<br /> 2007-02-03 | BOS | LAX<br /><br />I'd appreciate some help.<br
/><br/>FYI, table definitions for jsjourneys and jsports follow.<br /><br />js=# \d jsjourneys<br /> Table
"public.jsjourneys"<br/> Column | Type | <br /> Modifiers<br
/>---------------------+--------------------------+---------------------------------------------------------<br/> id |
bigint| not null default <br />nextval('jsjourneys_id_seq'::regclass)<br /> userid | bigint | not null<br /> typeid |
integer| not null<br /> carrier | integer |<br /> number | integer |<br /> departure_port | integer | not null<br />
arrival_port| integer | not null<br /> departure_gate | character varying |<br /> arrival_gate | character varying |<br
/>departure_date | date | not null<br /> fare_class | integer |<br /> scheduled_departure | timestamp with time zone
|<br/> scheduled_arrival | timestamp with time zone |<br /> actual_departure | timestamp with time zone |<br />
actual_arrival| timestamp with time zone |<br /> equipment | integer |<br /> notes | character varying(1500) |<br />
seat| character varying(4) |<br /> confirmation | character varying(20) |<br />Indexes:<br /> "jsjourneys_pkey" PRIMARY
KEY,btree (id)<br />Foreign-key constraints:<br /> "jsjourneys_arrival_port_fkey" FOREIGN KEY (arrival_port) <br
/>REFERENCESjsports(id)<br /> "jsjourneys_carrier_fkey" FOREIGN KEY (carrier) REFERENCES <br />jscarriers(id)<br />
"jsjourneys_departure_port_fkey"FOREIGN KEY (departure_port) <br />REFERENCES jsports(id)<br />
"jsjourneys_equipment_fkey"FOREIGN KEY (equipment) REFERENCES <br />jsequipment(id)<br /> "jsjourneys_fare_class_fkey"
FOREIGNKEY (fare_class) REFERENCES <br />jsfareclasses(id)<br /> "jsjourneys_typeid_fkey" FOREIGN KEY (typeid)
REFERENCES<br />jsjourneytypes(id)<br /> "jsjourneys_userid_fkey" FOREIGN KEY (userid) REFERENCES jsusers(id)<br /><br
/>js=#\d jsports<br /> Table "public.jsports"<br /> Column | Type | Modifiers <br /><br
/>-----------+-------------------+------------------------------------------------------<br/> id | integer | not null
default<br />nextval('jsports_id_seq'::regclass)<br /> code | character varying | not null<br /> city | character
varying| not null<br /> full_city | character varying | not null<br /> name | character varying |<br />Indexes:<br />
"jsports_pkey"PRIMARY KEY, btree (id)<br /> "jsports_index_city" btree (city)<br /> "jsports_index_code" btree
(code)<br/><br />Thanks!<br /><br />Colin<br /><br />---------------------------(end of
broadcast)---------------------------<br/>TIP 4: Have you searched our list archives?<br /><br />
http://archives.postgresql.org<br/></blockquote><p><hr /><font face="geneva" size="2"><b>Join Excite! - <a
href="http://www.excite.com"target="_blank">http://www.excite.com</a></b><br />The most personalized portal on the
Web!</font>