Hi Paul!

>Hi all,
>
>We're using Firebird 2.1.4 and I can't work out what's going on with the following queries.

(deleted queries returning 42 rows)

>Now the problem is that when I run the following I expect to get NO records returned (ignore
>the fact the query is pointless in this example):
>
>select A.ID
>from A
>where not A.ID in (select A.ID from A)
>and A.ID in
>(
> select B.ID
> from B,C
> where B.ID=C.BID;
>)
>
>But it still returns 42 records?!?
>
>This worked fine in InterBase 7.1 Has anybody got any idea what's going on here?

Not much of an idea what is wrong (excepting that the comma after C.BID isn't legal, but I guess you just copied and pasted rather than show us your real problem), I cannot see how your query could possibly return any rows (well, unless 'not A.ID' somehow manages to negate the ID and that negative ID actually exists, but I don't think Firebird does that). However, I suggest changing the query in two ways:

select A1.ID
from A A1
where not (A1.ID in (select A2.ID from A A2))
and A1.ID in
(select B1.ID
from B B1
join C C1 on B1.ID=C1.BID)

Actually, I'd recommend you to change the query quite a bit more (but the above should not return any rows):

select A1.ID
from A A1
left join A A2 on A1.ID = A2.ID
where A2.ID is null
and exists (select * from B B1
join C C1 on B1.ID=C1.BID
where A1.ID = B1.ID)

HTH,
Set