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
Work as if you don't need money
Love as if you've never been hurt before
Live as if this is your last day to live
Dance as if no one's watching