db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Buddy Testing of Using Clause
Date Wed, 05 May 2010 12:43:57 GMT
On 05/05/10 11:48, Jayaram Subramanian wrote:
> Hi,
> As per the review comments i used toursdb and executed the following
> queries and found them going through without issues
> 
> SELECT countries.* FROM COUNTRIES JOIN CITIES USING (COUNTRY)
> SELECT distinct countries.country,cities.country FROM COUNTRIES JOIN
> CITIES USING (COUNTRY)
> SELECT distinct countries.country FROM COUNTRIES LEFT JOIN CITIES
> USING (COUNTRY) WHERE CITIES.COUNTRY IS NULL
> 
> But i have a few clairifications in the following 2 scenarios
> Scenario 1
> =================
> When i tried doing
> SELECT  * FROM FLIGHTAVAILABILITY JOIN FLIGHTS ON (FLIGHT_ID) WHERE
> FLIGHT_DATE > '2004-04-20'
> I got an error message stating "Column name 'FLIGHT_ID' is in more
> than one table in the FROM list.  Do we need to issue the query
> differently?

I think there's a typo in the query. It says ON instead of USING. If I
change it from ON to USING, it works.

> Scenario 2
> ============
> Given a scenario where we have to get flights originating from a given
> city (Ex:Seattle)
> How can we use the "using" clause in this scenario given that the
> column name is "ORIG_AIRPPORT" in flights table and "AIPRORT" in the
> cities table?...

That's trickier... :) But you can write such a query with USING if you
assign a correlation name to the column with an AS clause:

select flight_id from
  flights AS fl(flight_id, segment_number,
                airport, depart_time,
                dest_airport, arrive_time,
                meal, flying_time,
                miles, aircraft)
  join cities using (airport)
  where city_name = 'Seattle'

or

select flight_id from
  (select flight_id, orig_airport AS airport from flights) f
  join cities using (airport)
  where city_name = 'Seattle'


-- 
Knut Anders

Mime
View raw message