Message-ID: <47869A9C.7010203@denterprises.org>
Date: Thu, 10 Jan 2008 17:22:20 -0500
From: Colin Wetherbee <cww@denterprises.org>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: Paul Lambert <plengada@optusnet.com.au>
CC: pgsql-sql@postgresql.org
Subject: Re: JOIN a table twice for different values in the same query
References: <47869704.8050306@denterprises.org>
	<478699D0.7070608@optusnet.com.au>
In-Reply-To: <478699D0.7070608@optusnet.com.au>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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