pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJOIN a table twice for different values in the same query
9+ messages / 5 participants
[nested] [flat]
* JOIN a table twice for different values in the same query
@ 2008-01-10 22:07 Colin Wetherbee <cww@denterprises.org>
0 siblings, 2 replies; 9+ messages in thread
From: Colin Wetherbee @ 2008-01-10 22:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JOIN a table twice for different values in the same query
@ 2008-01-10 22:18 Paul Lambert <plengada@optusnet.com.au>
parent: Colin Wetherbee <cww@denterprises.org>
1 sibling, 1 reply; 9+ messages in thread
From: Paul Lambert @ 2008-01-10 22:18 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JOIN a table twice for different values in the same query
@ 2008-01-10 22:21 Daniel Hernandez <breydan@excite.com>
0 siblings, 0 replies; 9+ messages in thread
From: Daniel Hernandez @ 2008-01-10 22:21 UTC (permalink / raw)
To: cww@denterprises.org; pgsql-sql
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 < cww@denterprises.org > wrote:From: Colin Wetherbee [mailto: cww@denterprises.org]To: pgsql-sql@postgresql.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!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JOIN a table twice for different values in the same query
@ 2008-01-10 22:22 Colin Wetherbee <cww@denterprises.org>
parent: Paul Lambert <plengada@optusnet.com.au>
0 siblings, 0 replies; 9+ messages in thread
From: Colin Wetherbee @ 2008-01-10 22:22 UTC (permalink / raw)
To: Paul Lambert <plengada@optusnet.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JOIN a table twice for different values in the same query
@ 2008-01-10 22:23 Phillip Smith <phillip.smith@weatherbeeta.com.au>
parent: Colin Wetherbee <cww@denterprises.org>
1 sibling, 1 reply; 9+ messages in thread
From: Phillip Smith @ 2008-01-10 22:23 UTC (permalink / raw)
To: 'Colin Wetherbee' <cww@denterprises.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JOIN a table twice for different values in the same query
@ 2008-01-10 22:30 Colin Wetherbee <cww@denterprises.org>
parent: Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 1 reply; 9+ messages in thread
From: Colin Wetherbee @ 2008-01-10 22:30 UTC (permalink / raw)
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JOIN a table twice for different values in the same query
@ 2008-01-10 22:40 Colin Wetherbee <cww@denterprises.org>
parent: Colin Wetherbee <cww@denterprises.org>
0 siblings, 1 reply; 9+ messages in thread
From: Colin Wetherbee @ 2008-01-10 22:40 UTC (permalink / raw)
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JOIN a table twice for different values in the same query
@ 2008-01-15 13:20 Magne Mæhre <Magne.Mahre@Sun.COM>
parent: Colin Wetherbee <cww@denterprises.org>
0 siblings, 1 reply; 9+ messages in thread
From: Magne Mæhre @ 2008-01-15 13:20 UTC (permalink / raw)
To: Colin Wetherbee <cww@denterprises.org>; +Cc: Phillip Smith <phillip.smith@weatherbeeta.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JOIN a table twice for different values in the same query
@ 2008-01-15 14:03 Colin Wetherbee <cww@denterprises.org>
parent: Magne Mæhre <Magne.Mahre@Sun.COM>
0 siblings, 0 replies; 9+ messages in thread
From: Colin Wetherbee @ 2008-01-15 14:03 UTC (permalink / raw)
To: Magne Mæhre <Magne.Mahre@Sun.COM>; +Cc: Phillip Smith <phillip.smith@weatherbeeta.com.au>; pgsql-sql
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 something like 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2008-01-15 14:03 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-10 22:07 JOIN a table twice for different values in the same query Colin Wetherbee <cww@denterprises.org>
2008-01-10 22:18 ` Paul Lambert <plengada@optusnet.com.au>
2008-01-10 22:22 ` Colin Wetherbee <cww@denterprises.org>
2008-01-10 22:23 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2008-01-10 22:30 ` Colin Wetherbee <cww@denterprises.org>
2008-01-10 22:40 ` Colin Wetherbee <cww@denterprises.org>
2008-01-15 13:20 ` Magne Mæhre <Magne.Mahre@Sun.COM>
2008-01-15 14:03 ` Colin Wetherbee <cww@denterprises.org>
2008-01-10 22:21 Re: JOIN a table twice for different values in the same query Daniel Hernandez <breydan@excite.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox