Thank you.


>Indeed, the UNION is the way to obtain the equivalent of an outer join, up to VFP 3.
>
>You have to combine two table results with a UNION; something like this:
>
>

>SELECT PROJECTS.PROJID,;
>	PROJECTS.PROJ_NAME,;
>	CNS_NOTE.CSNT_NOTE;
>	FROM CONSULT, PROJECTS,CONSLIST,CNS_NOTE;
>	WHERE PROJECTS.PROJID = CONSLIST.PROJID ;
>		AND CONSLIST.CONSKEY  =  CONSULT.CONSKEY ;
>		AND CONSULT.CONSKEY = CNS_NOTE.CONSKEY	;
>		AND (PROJECTS.PJ_STATUS="ACQ-CONTRACT" ;
>		AND (CONSLIST.TYPE="CNS" ;
>		OR (CONSLIST.TYPE="GPX"));
>		AND PROJECTS.PACQASSOC="SFG") ;
> UNION
>    SELECT PROJECTS.PROJID...;
>    WHERE projects.projid not in (select projid fom cns_note);
>	INTO CURSOR VPDEALS6
>

>
>The tricky part is to get the fields in both part of the UNION to match exactly - field by field. In case of doubt, run the parts separately, and analyze the structure.
>
>>Hello all.
>>I need help running this on Foxpro 2.6W( 2.6 does not understand INNER/LEFT/RIGHT e.tc. joins).
>>
>>SELECT PROJECTS.PROJID,;
>> PROJECTS.PROJ_NAME,;
>> CNS_NOTE.CSNT_NOTE;
>> FROM CONSULT, PROJECTS,CONSLIST,CNS_NOTE;
>> WHERE PROJECTS.PROJID = CONSLIST.PROJID ;
>> AND CONSLIST.CONSKEY = CONSULT.CONSKEY ;
>> AND CONSULT.CONSKEY = CNS_NOTE.CONSKEY ;
>> AND (PROJECTS.PJ_STATUS="ACQ-CONTRACT" ;
>> AND (CONSLIST.TYPE="CNS" ;
>> OR (CONSLIST.TYPE="GPX"));
>> AND PROJECTS.PACQASSOC="SFG") ;
>> INTO CURSOR VPDEALS6
>>
>>The above statement works fine. But I also need all the record from CONSULT that doesn't have a match in CNS_NOTE table. I'm trying to use a UNION statemet but I can't get it to work properly.
>>
>>Please help if you can.
>>
>>Thanks