Re: Problem in dbaccess (Informix 7.2)

} Little problem. } We have 2 tables A and B. In table A we have column numer (SERIAL type). In } B we have column A_id. which is a key to A.numer (is this clear?). In table } A we have 80000 records, in B 20000 records. } And now we're making 3 selects: } 1) select count(*) from A; } Answer: 80000. } 2)select count(*) from A where numer in (select A_id from B); } Answer: 20000. } 3)select count(*) from A where numer not in (select A_id from B); } Answer: 0 } Question: the 3rd select must return 60000, or not? Why there is zero? What } is the tip? Try "select count(*) from B where A_id is null". If you have any nulls, query 3 will not work as it is written. Modify it to be "select count(*) from A where numer not in (select A_id from B where A_id is not null)". That should get 60000. Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them.