RE: Problem in dbaccess (Informix 7.2)
We ran into the same problem some time ago. I beleive the statement is
correct but the engine is having a problem with it. what I do instead
of 'not in' I would use the following:
select count(*) from A where 0 = (select count(*) from B where A.id =
B.id)
For some reason I have found that this syntax is has less problems with
informix.
Russ...
}-----Original Message-----
}From: Tomek Frydryk [SMTP:tomekf@sylaba.poznan.pl]
}Sent: Wednesday, October 15, 1997 4:57 AM
}To: informix-list@rmy.emory.edu
}Subject: 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?
}
}Tomek Frydryk
}Bank Staropolski S.A.
}
}
}