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.

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

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

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 ;
	left join Trans_Insurances TI ;
	on curTemp.Trans_pk = TI.cTrans_fk ;
	INNER join Client_Product_Lines ;
	on curTemp.cClient_Product_Lines_fk = Client_Product_Lines.cClient_product_lines_pk ;
	into cursor (m.tcAlias)	readwrite

Would you please help?

Thanks in advance.