Select query and use of IN

[Bart Pietercil]

Hi List,


   "SELECT recid, competence_name FROM competence_cards WHERE org_ptr  
= 1 AND recid NOT IN (SELECT compcard_ptr FROM  
mm_compcard_functprofile WHERE profile_ptr = 1)"


This query is not returning the expected result (expected = all  
records, returned = empty) when the subselect is empty but works when  
the subselect has recid's?

How would I need to rewrite I so that all recid from competence_cards  
(with org_ptr= 1) are returned when there are no compcard_ptr with  
profile_ptr = 1 in mm_compcard_functprofile

TIA

BArt Pietercil