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
