db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jørgen Løland <Jorgen.Lol...@Sun.COM>
Subject Re: having a problem with not in
Date Mon, 29 Oct 2007 10:18:58 GMT
Øystein Grøvlen wrote:
> Jørgen Løland wrote:
>> tyoup wrote:
>>> i'm having problems with the following query
>>>
>>> select a,b,c from ta where (b,c) not in (select bb,cc from tb where 
>>> dd = 3)
>>>
>>> wich won't work in derby
>>> how can i work this around ?
>>>
>>
>> Hi Tyoup
>>
>> If I remember correctly, IN clauses can only take handle one 
>> attribute. I think you'll get what you want if you rewrite the query to:
>>
>> select a,b,c
>> from ta
>> where not (b in (select bb from tb where dd=3) and
>>            c in (select cc from tb where dd=3));
>>
> 
> I do not think this is right since it is ok that the b and c values 
> exists in different rows of tb, but not in the same row.  I think the 
> two queries below should work:
> 
> select a,b,c
> from ta
> where not exists (select * from tb where dd=3 and b=bb and c=cc)
> 
> select a, b, c from ta except
> select a, b, c from ta, tb where dd=3 and b = bb and c = cc

Ahh... you're right Øystein. I used a too simple test case.

-- 
Jørgen Løland

Mime
View raw message