Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 7E3B02E4706 for ; Thu, 10 Jan 2008 18:22:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 54539-06 for ; Thu, 10 Jan 2008 18:21:53 -0400 (AST) Received: from excite.com (nn6.excitenetwork.com [207.159.120.60]) by postgresql.org (Postfix) with ESMTP id F17ED2E46F9 for ; Thu, 10 Jan 2008 18:21:56 -0400 (AST) Received: by xprdmxin.myway.com (Postfix, from userid 110) id 639A58B31B; Thu, 10 Jan 2008 17:21:53 -0500 (EST) To: cww@denterprises.org, pgsql-sql@postgresql.org Subject: Re: JOIN a table twice for different values in the same query Received: from [201.170.165.111] by xprdmailfe9.nwk.excite.com via HTTP; Thu, 10 Jan 2008 17:21:53 EST X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: ID = 91b5991f7b331960b2910c492581391e Reply-To: breydan@excite.com From: "Daniel Hernandez" MIME-Version: 1.0 X-Sender: breydan@excite.com X-Mailer: PHP Content-Type: multipart/alternative; boundary="EXCITEBOUNDARY_000__b5235ab28d78fd6bc2bb0a69d2725cf0"; Content-Transfer-Encoding: 7bit Message-Id: <20080110222153.639A58B31B@xprdmxin.myway.com> Date: Thu, 10 Jan 2008 17:21:53 -0500 (EST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/79 X-Sequence-Number: 29932 --EXCITEBOUNDARY_000__b5235ab28d78fd6bc2bb0a69d2725cf0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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! --EXCITEBOUNDARY_000__b5235ab28d78fd6bc2bb0a69d2725cf0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: 7bit js=# SELECT departure_date, departure.code AS departure_code, arrival.code as arraival_code
FROM jsjourneys
        JOIN jsports as departure ON jsjourneys.departure_port = departure.id
       JOIN jsports as arrival on jsjourneys.arraival_port = arraival.id
LIMIT
4;

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.org
Date: Thu, 10 Jan 2008 17:07:00 -0500
Subject: [SQL] 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 | 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! --EXCITEBOUNDARY_000__b5235ab28d78fd6bc2bb0a69d2725cf0--