Hi Hugo,

Either your simplification is incorrect or NULLs are involved somehow.


>
>I will try tomorrow to use exists to see what happens, although I believe I am actually using exists, and changed it to what I posted as a simplification of the problem.
>EventsLog has little less than one million records and FaultsLog has little more than 300 hundred thousand records.
>
>
>>I prefer to write such queries using EXISTS operator

>>select EventsLog.PK 
>>	from TVM.dbo.EventsLog 
>>	where not exists (select 1 from tvm.dbo.FaultsLog WHERE EventsLog.PK = FaultsLog.fkEventsLog)
>>

>>BTW, are there any records in the FaultsLog table?
>>
>>>I have two tables EventsLog and FaultsLog, FaultsLog has a foreign key to EventsLog and I run this queries
>>>
>>>

>>>select EventsLog.PK from TVM.dbo.EventsLog where PK = 1095387
>>>
>>>select FaultsLog.PK, FaultsLog.fkEventsLog from TVM.dbo.FaultsLog where FaultsLog.fkEventsLog = 1095387
>>>
>>>select EventsLog.PK from TVM.dbo.EventsLog where EventsLog.PK not in (select FaultsLog.fkEventsLog from tvm.dbo.FaultsLog)
>>>

>>>
>>>First query returns one record, all is good.
>>>Second query returns no records, still good.
>>>Third query returns no records too?? Shouldn't I get the record for PK = 1095387?
>>>
>>>So confused...
>>>
>>>
>>>(By the way, this is part of a longer store procedure that was working fine until Friday, today I noticed the problem and I think I narrowed it down to a query similar to this)
>>>
>>>
>>>TIA,
>>>
>>>Hugo