>Hi everybody,
>
>I'm having trouble writing this complex select.
>
>I have a cursor curTemp, which has cTrans_pk.
>
>I want to add Carriers.cShort_Name to my final select statement.
>
>From cTrans_pk I can get to Carriers this way:
>

>INNER JOIN Trans_Insurances TI on curTemp.cTrans_pk = TI.cTrans_fk ;
>INNER JOIN Insurances IN on TI.cInsurances_pk = IN.cInsurances_pk ;
>INNER JOIN Carrier_Branches CB on IN.cCarrier_Branches_fk = ;
>CB.cCarrier_Branches_pk ;
>INNER JOIN Carriers on CB.cCarriers_fk = Carriers.cCarriers_pk

>
>My first problem is that my curTemp may not have matching records in Trans_Insurances. Should I put LEFT in the first JOIN and INNER in the rest? That's my first confusion.
>
Uncertainty is correct, because the declaration seems ambiguous (but it is not true).
To render explicit the all owe urare the parentheses

She tries to understand this example, and then the answer should be evident

CREATE CURSOR AA (II I)
INSERT INTO AA VALUES (0)
INSERT INTO AA VALUES (1)
INSERT INTO AA VALUES (2)

SET NULLDISPLAY TO '?'

* 0 1 2
* 1 2 ?
* 2 ? ?
SELECT * FROM AA A1;
	LEFT JOIN AA A2 ON A2.II=A1.II+1;
	LEFT JOIN AA A3 ON A3.II=A1.II+2

* 0 1 ?
* 1 2 ?
* 2 ? ?
SELECT * FROM AA A1;
	LEFT JOIN AA A2 ON A2.II=A1.II+1;
	LEFT JOIN AA A3 ON A3.II=A2.II+2

* 0 1 2
SELECT * FROM AA A1;
	LEFT  JOIN AA A2 ON A2.II=A1.II+1;
	INNER JOIN AA A3 ON A3.II=A1.II+2

* Error
SELECT * FROM AA A1;
	LEFT  JOIN (AA A2 INNER JOIN AA A3 ON A3.II=A1.II + 2 )  ON A2.II=A1.II+1

*
SELECT * FROM AA A1;
	LEFT  JOIN AA A2 ON A2.II=A1.II+1;
	INNER JOIN AA A3 ON A3.II=A2.II+2

* 0 ? ?
* 1 ? ?
* 2 ? ?
SELECT * FROM AA A1;
	LEFT  JOIN (AA A2 INNER JOIN AA A3 ON A3.II=A2.II + 2 )  ON A2.II=A1.II+1

>Secondly, I may have multiple insurances per Trans. In this case I only want the insurance with max iSequence_Number in my final select.

You uses TI.cInsurances_pk and this is a 1-1 relation to me

For 1-M it should to be TI.cInsurances_fk

to every way, the correct SQL syntax is

left join (
  ON joincondition and insurance.iSequence_Number ;
    = (SELECT MAX(iSequence_Number) FROM insurance I WHERE ... )

but VFP don't support it.

Then put it into the global where, and with a LEFT JOIN
you have to add a IS NULL free condition on a not nullable field ( like a pk )

WHERE TI.pk IS NULL OR IN.iSequence_Number ;
   IN (select max(iSequence_Number) from Insurances WHERE [the condition for the insurances subset])

>
>Here is from what I started and not sure how to continue...
>

continue with

 select curTemp.*, padr(nvl(alltrim(Names.cl_name) + ", ","") + ;
	nvl(alltrim(Names.cf_name) + " ","") + ;
	nvl(Names.cm_initial,""),50) as cName, ;
	evl(nvl(Carriers.cShort_Name,padr('NONE',10)),left(Carriers.cCarrier_Name,10)) as cCarrier, ;
	Client_Product_Lines.cProduct_line ;
	from curTemp INNER join Names on ;
	curTemp.cPatients_pk = Names.cPointer_fk ;
	INNER join Client_Product_Lines ;
	on curTemp.cClient_Product_Lines_fk = Client_Product_Lines.cClient_product_lines_pk ;
        LEFT JOIN ;
        (Trans_Insurances TI;
         INNER JOIN Insurances IN on TI.cInsurances_pk = IN.cInsurances_pk ;
         INNER JOIN Carrier_Branches CB on IN.cCarrier_Branches_fk = CB.cCarrier_Branches_pk ;
         INNER JOIN Carriers on CB.cCarriers_fk = Carriers.cCarriers_pk);
         on curTemp.cTrans_pk = TI.cTrans_fk ;
    WHERE TI.???pk IS NULL OR IN.iSequence_Number ;
         IN (select max(iSequence_Number) from Insurances WHERE [the condition for the insurances subset]);
   into cursor (m.tcAlias)	readwrite

I cannot to write the [the condition for the insurances subset]
because I don't known the database rules.

>Would you please help?
>
>Thanks in advance.