>select * from tblclaimheader ch > where ch.ilsid not in (select l.ilsid from tbllocation L) >-- returns no records ???? ARE YOU SURE ???? MUST RETURN 1 OR MORE ROWS >select * from tblclaimheader ch where ch.ilsid = 1 and > ch.ilsid not in (select l.ilsid from tbllocation L where l.ilsid = 1) >-- returns 1 record can be > >select ch.* from tblclaimheader ch where not exists (select * from tbllocation L where l.ilsid = ch.ilsid) >-- returns 1841 records can be >select * from tblclaimheader where ilsid = 1 >-- returns 1 record sure >select * from tbllocation where ilsid = 1 >-- returns no records sure