Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
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
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement