db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jayaram Subramanian <rsjay1...@gmail.com>
Subject Re: Buddy Testing of Using Clause
Date Wed, 05 May 2010 09:48:58 GMT
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?

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?...

With Regards
Jayaram

On Sun, May 2, 2010 at 11:47 AM, Jayaram Subramanian
<rsjay1976@gmail.com> wrote:
> Thanks a lot.... I will proceed based on the comments.
>
> With Regards
> Jayaram
>
> On Sun, May 2, 2010 at 11:32 AM, Lily Wei <lilywei@yahoo.com> wrote:
>> Hi Jayaram:
>>    On top of Derby reference menu, this is some join example with using
>> keyword for you to reference too.
>>  http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/SimplifyingJoinswiththeUSINGKeyword.htm
>>
>> Lily
>>
>> ________________________________
>> From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
>> To: derby-dev@db.apache.org
>> Sent: Sun, May 2, 2010 5:33:15 AM
>> Subject: Re: Buddy Testing of Using Clause
>>
>> On 05/ 1/10 01:23 PM, Jayaram Subramanian wrote:
>>> Hi,
>>>
>>> I am trying to test along the comments. I am taking the 10.5 as
>>> reference .. Is that correct?
>>
>> The USING clause was not supported in 10.5, so you won't find anything
>> about it in the 10.5 manuals. You can find the latest development
>> version of the reference guide here:
>> http://db.apache.org/derby/docs/dev/ref/
>>
>>> Also when i saw 10.5 reference guide for
>>> join i saw the following sql
>>>
>>> SELECT SAMP.EMP_ACT.*, LASTNAME
>>> FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
>>> ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
>>>
>>> Is there any samp schema which gets installed along with Derby install?
>>>
>>
>> I don't think so. I suspect this example is based on a DB2 sample
>> database (at least it looks like many of the examples in the DB2 manual
>> use a similar database). Many other examples in the Derby manuals use
>> toursdb, which you can find under java/demo/toursdb/toursdb after you
>> have run ant all.
>>
>> --
>> Knut Anders
>>
>>
>>
>

Mime
View raw message