Very Computer

1. Strange behaviour of 'NOT IN'

Hi,

I'm having a curious frustrating problem with 'NOT IN'.
I have a table defining 'unities' (TUnidades) which has 13 different lines.
Its identifier is 'Id'.
I have another table defining people (TPessoas), with foreign key to
TUnidades, which is 'unid_Id'. This field has 12 distinct values in the
whole table.

Now, I want to get all those values of TUnidades.Id which do not exist in
TPessoas.Unid_Id. So, I write the following query

select Id from tunidade
where Id not in
(select distinct unid_Id from TPEssoas).

I should get 1 record, but I get 0!

If I rewrite slightly the query, like this,

select Id from tunidade
where Id not in
(select distinct unid_Id from TPEssoas
where unid_Id <> 0)

or any other condition over unid_Id that doesn't affect the result (there is
no record where unid_Id is 0), I get the right result, with 1 record.

Is this a flaw of the SQL, or of the engine ? How can I know if I have more
of these errors undetected ?

Thank you

Alexandre Pinto

Novabase Porto
Sistema de Informa??o Interno

2. Where can I order Paradox 8 standalone version?

3. Strange Grid Combobox Behaviour (same value shown in every row)

4. SQLServer Database Devices list

5. Strange behaviour of bcp and BULK INSERT

6. Use filter criteria in JOIN clause or WHERE clause?

7. Strange Insert Behaviour

8. APPLICATION DEVELOPER/ Intranet Applications, Extranet applications- WA

9. Not sure where to put this--strange ODBC behavior (or I think it's strange)

10. ADO Recordset's strange behaviour when filtered using adFilterConflictingRecords

11. Strange behaviour: Not accepting connections

12. Strange Behaviour VB6 ADO MSACCESS Added Records do not appear immediately