At 02:35 PM 09-10-00 +0200, you wrote:

>Hello,
>
>Can someone explain me this problem.
>
>I have two table :
>Create Table ToDoList
>(
>Code Varchar(15) Not NULL,
>User Varchar(15),
>Date DateTime,
>..
>Primary key(Code)
>)
>
>A ToDoListDone which have the same structure as ToDoList
>with a new field CodeFrom Varchar(15) which contain the source code of
>ToDoList (When something to do become done).
>
>The strange result :
>
>Query1=
>Select * from ToDoList
>=> return me 15 records.
>
>When a Query2=
>Select * from ToDoList
>where ToDoList.code in (select codefrom from ToDoListDone);
>=> Return me 2 records
>
>A Query3=
>Select * from ToDoList
>where ToDoList.code not in (select codefrom from ToDoListDone);
>=> return me 0 record !!!!!
>
>Can somebody explain me why not Query1=Query2+Query3 ?
>
>I have found a solution:
>Select * from ToDoList
>where ToDoList.code not in (select codefrom from ToDoListDone where codefrom
>is not null);
>=> retourn me 13 records, and Query1=Query2+Query3 !!!!
>
>Is it a bug ?

Yes - a logic bug in your SQL. <g>

For the third query, try
Select * from ToDoList
where (NOT (ToDoList.code in (select codefrom from ToDoListDone where
codefrom is not null)));

It's very inefficient! If you really NEED two tables, use joins and indexes.

I would suggest you need only one table, with a Boolean-style column
"Done"....?

Helen



All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
___________________________________________________