Thread: 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 |
notnull 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 |
timestampwith 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 |
notnull 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
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
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>
Paul Lambert wrote: > Colin Wetherbee wrote: >> 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; >> > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend Ah, I didn't realize you could alias tables inside the JOIN. Excellent. It works. :) js=# SELECT departure_date, dp.code AS departure_code, ap.code AS arrival_code FROM jsjourneys JOIN jsports dp ON jsjourneys.departure_port = dp.id JOIN jsports ap ON jsjourneys.arrival_port = ap.id LIMIT 4; departure_date | departure_code | arrival_code ----------------+----------------+-------------- 2006-11-19 | BHM | ATL 2006-11-16 | PIT |ATL 2006-11-16 | ATL | BHM 2006-10-26 | PIT | BOS (4 rows) For archive completeness, note the query is joined relative to dp.id and ap.id, rather than jsports.id. Thanks for your help! Colin
Try something like this where we alias the joined tables: SELECT departure_date, j1.code AS departure_code, j2.code AS arrival_code FROM jsjourneys LEFT OUTER JOIN jsports AS j1 ON jsjourneys.departure_port = j1.id LEFT OUTER JOIN jsports AS j2 ON jsjourneys.arrival_port = j2.id; As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just the code) Cheers, ~p THINK BEFORE YOU PRINT - Save paper if you don't really need to print this *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail.
Phillip Smith wrote: > As a side note - all the IATA codes are unique for each airport - > wouldn't it be better to use these as the Primary Key and Foreign > Keys? Then you wouldn't have to even join the tables unless you > wanted the port names (not just the code) This is true, but FWIW, my application will mostly be joining for the name of the airport or the city, not the code. I'll keep the idea of using the codes as keys in mind, though. Thanks for pointing that out. Colin
Colin Wetherbee wrote: > Phillip Smith wrote: >> As a side note - all the IATA codes are unique for each airport - >> wouldn't it be better to use these as the Primary Key and Foreign >> Keys? Then you wouldn't have to even join the tables unless you >> wanted the port names (not just the code) > > This is true, but FWIW, my application will mostly be joining for the > name of the airport or the city, not the code. > > I'll keep the idea of using the codes as keys in mind, though. Thanks > for pointing that out. Oh, now I remember why I'm using IDs as keys. ;) The code isn't always going to be an airport, and, for example, a train station in Buenos Aires could conceivably have the same code as a shipping port in Rotterdam, which, in turn, might well be JFK. :) Colin
Colin Wetherbee wrote: > Colin Wetherbee wrote: >> Phillip Smith wrote: >>> As a side note - all the IATA codes are unique for each airport - >>> wouldn't it be better to use these as the Primary Key and Foreign >>> Keys? Then you wouldn't have to even join the tables unless you >>> wanted the port names (not just the code) >> >> This is true, but FWIW, my application will mostly be joining for the >> name of the airport or the city, not the code. >> >> I'll keep the idea of using the codes as keys in mind, though. Thanks >> for pointing that out. > > Oh, now I remember why I'm using IDs as keys. ;) > > The code isn't always going to be an airport, and, for example, a train > station in Buenos Aires could conceivably have the same code as a > shipping port in Rotterdam, which, in turn, might well be JFK. :) Note that IATA codes are _NOT_ unique. The current list of IATA trigrams list upward of 300 duplicate codes. If you include the train stations, there might be additional collisions. You could consider using the ICAO four-letter identifiers instead. They are unique, and are preferred by airspace management authorities. A mapping to the corresponding IATA code exists. --Magne
Magne Mæhre wrote: > Colin Wetherbee wrote: >> Colin Wetherbee wrote: >>> Phillip Smith wrote: >>>> As a side note - all the IATA codes are unique for each airport - >>>> wouldn't it be better to use these as the Primary Key and Foreign >>>> Keys? Then you wouldn't have to even join the tables unless you >>>> wanted the port names (not just the code) >>> >>> This is true, but FWIW, my application will mostly be joining for the >>> name of the airport or the city, not the code. >>> >>> I'll keep the idea of using the codes as keys in mind, though. >>> Thanks for pointing that out. >> >> Oh, now I remember why I'm using IDs as keys. ;) >> >> The code isn't always going to be an airport, and, for example, a >> train station in Buenos Aires could conceivably have the same code as >> a shipping port in Rotterdam, which, in turn, might well be JFK. :) > > Note that IATA codes are _NOT_ unique. The current list of IATA > trigrams list upward of 300 duplicate codes. If you include the train > stations, there might be additional collisions. > > You could consider using the ICAO four-letter identifiers instead. They > are unique, and are preferred by airspace management authorities. A > mapping to the corresponding IATA code exists. I have both ICAO and IATA codes in my database, but users who typically won't know (or even be aware of) ICAO codes will be using the front end. In fact, in the front end, the users will see somethinglike the following (with the respective, unique, application-specific port ID hidden in the background). Houston, TX (IAH - George Bush Intercontinental Airport) New York, NY (JFK - John F. Kennedy International Airport) Dubai, United Arab Emirates (DXB - Dubai International Airport) Which should be unique enough. :) Colin