>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