In SQL, when you define a join condition i the way you're doing it, it comes out as a inner join. I.E. the result set contains the records that have match in both tables only. If there is a record from table 1 that the sql doesn't find a match in table 2, then it isn't included in the result set.

In order to do what you're seeking, you have to explicitly make a left join, the syntax being:

SELECT BALANCE.* FROM BALANCE left join PTYPES on BALANCE.TYPE=PTYPES.TYPE where EMPTY(PTYPES.TYPE) INTO DBF ANYTABLE

>OK, what am I missing here?
>
>I have two files: BALANCE and PTYPES
>
>PTYPES is ordered on the basis of its TYPE field
>
>BALANCE is related to PTYPES through the TYPE field
>
>Every TYPE in BALANCE should have a match in PTYPES.
>
>I deliberately alter a TYPE in BALANCE to ensure that at least one record does not have a match.
>
>If I SET FILTER TO EMPTY(PTYPES.TYPE) I duly find the erroneous record.
>
>If I SELECT BALANCE.* FROM BALANCE,PTYPES WHERE BALANCE.TYPE=PTYPES.TYPE AND NOT EMPTY(PTYPES.TYPE) INTO DBF ANYTABLE
>
>it duly selects all the correct records and omits the faulty one.
>
>Yet, if I try to do the opposite (in order to detect the faulty one)
>i.e.
>
>SELECT BALANCE.* FROM BALANCE,PTYPES WHERE BALANCE.TYPE=PTYPES.TYPE AND EMPTY(PTYPES.TYPE) INTO DBF ANYTABLE
>
>I get zilch.
>
>Pourquoi?
>
>Harry