From: | Colin Wetherbee <cww(at)denterprises(dot)org> |
---|---|
To: | Paul Lambert <plengada(at)optusnet(dot)com(dot)au> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: JOIN a table twice for different values in the same query |
Date: | 2008-01-10 22:22:20 |
Message-ID: | 47869A9C.7010203@denterprises.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Phillip Smith | 2008-01-10 22:23:12 | Re: JOIN a table twice for different values in the same query |
Previous Message | Daniel Hernandez | 2008-01-10 22:21:53 | Re: JOIN a table twice for different values in the same query |