Ø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