db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Øystein Grøvlen <Oystein.Grov...@Sun.COM>
Subject Re: having a problem with not in
Date Mon, 29 Oct 2007 09:07:42 GMT
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

--
Øystein

Mime
View raw message