Re: having a problem with not in

[tyoup <ty...@ifrance.com>]

thank you for pointing out the request with the "not exists" syntax
for some reason i won't use the "except" syntax since i would have te
write almost twice the same request
i was stuck with "not in" since it was working with oracle
it was confusing

thank you very much

Tyoup


oysteing 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
> 
> --
> Øystein
> 
> 

-- 
View this message in context: http://www.nabble.com/having-a-problem-with-not-in-tf4703857.html#a13556638
Sent from the Apache Derby Users mailing list archive at Nabble.com.