>>I've been trying to create an SQL statement which relates information from 3 tables. The problem is that there is only a common link between two of the tables. In order to get the information I needed from the third table, I had to go through an intermediate table (the order header table which I didn't really want) to get the link for my names and addresses. The resulting third SQL worked, but the whole process seemed rather clunky and unnecessary to me. I kept going back and forth between wanting to use a union and some kind of subquery, but could never get it all to come together until I broke it apart :)
>>
>>Can anyone help me streamline this - I'm sure I'll run into this again and want to do it the right way?
>>
>>Here's the code I ended up with:
>>
>>SELE ev_eventdesc, ol_quantity, oL_orderno ;
>> FROM confdata!cfevents LEFT JOIN confdata!cforderl ;
>> ON Cforderl.ol_evcode = Cfevents.ev_code ;
>> WHERE SUBSTR(Cfevents.ev_code,1,4) = ALLT(STR(YEAR(DATE()))) ;
>> INTO CURSOR Cutemp1
>>
>>SELE ev_eventdesc, ol_quantity, ol_orderno, ;
>> oh_peoplid ;
>> FROM Cutemp1 LEFT JOIN Confdata!cforderh ;
>> ON oh_orderno = ol_orderno ;
>> INTO CURSOR Cutemp2
>>
>>SELE ev_eventdesc, ol_quantity, pe_firstna, ;
>>pe_middlei, pe_lastnam ;
>>FROM Cutemp2 LEFT JOIN Confdata!Cfpeople ;
>>on pe_peoplid = oh_peoplid
>>
>>
>>Thanks in advance for the help.
>>
>>Sylvia
>Sylvia,
>

SELE ev_eventdesc, ol_quantity, pe_firstna, ;
>	pe_middlei, pe_lastnam ;
>  FROM confdata!cfevents ;
>  	LEFT JOIN confdata!cforderl ;
>  		ON Cforderl.ol_evcode = Cfevents.ev_code ;
>  	LEFT JOIN Confdata!cforderh ;
>		ON oh_orderno = ol_orderno ;
>	LEFT JOIN Confdata!Cfpeople ;
>		on pe_peoplid = oh_peoplid	;
>   WHERE substr(Cfevents.ev_code,1,4) = ALLT(STR(YEAR(DATE()))) ;
>   INTO CURSOR crsFinal

Try this one. Seemed OK at first glance.
>Cetin

Cetin -

Thanks - this worked. The thing which confused me is that I needed to go through that order header table to get to the link which provided me with my people names in the people table. If I understand the way these left joins work, each left join is combined with the previous result set - that's why you were able to eliminate the intermediate cursor. Is that correct? I guess I was thinking that each left join was combining with the original FROM table (in this case cfevents).

Sylvia