At 04:20 AM 14/11/2009, you wrote:

>E.g. If table1 with 3 records with id '1', '2', '3'
>I use following statementon table1
>
>SELECT id FROM table1
>WHERE id IN ('1', '2', '3')
>AND NOT id IN ('2', '3')
>
>the correct result is '1'.
>
>
>if I use Subqueries
>
>SELECT id FROM table1
>WHERE id IN (SELECT id FROM table1 WHERE id IN('1', '2', '3')) AND NOT id IN (SELECT id FROM table1 WHERE id IN('2', '3'))
>
>the correct result is '1'.
>
>Subquery with INNER JOIN:
>
>SELECT id FROM table1
>WHERE id IN (SELECT A.ID FROM table1 A JOIN table1 B ON B.ID = A.ID WHERE B.ID IN('1', '2', '3')) AND NOT id IN (SELECT A.ID FROM table1 A JOIN table1 B ON B.ID = A.ID WHERE B.ID IN('2', '3'))
>
>the wrong result is '1', '2', '3'.
>
>This is FB 2.1.3. Any ideas what's wrong?

I think you have 3 confused cursors on table1.

Try it this way instead and report back:

SELECT t1.id FROM table1 t1
WHERE
t1.id IN (SELECT A.ID FROM table1 A
JOIN table1 B ON B.ID = A.ID
WHERE B.ID IN('1', '2', '3'))
AND
NOT t1.id IN (SELECT A1.ID FROM table1 A1
JOIN table1 B1 ON B1.ID = A1.ID
WHERE B1.ID IN('2', '3'))

./helen