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.
