>Hi everybody,
>
>I'd like to find all cases where I have only one guarantor per trans and this guarantor (in Trans_Relateds table) has iGuarantor_Flag = 1, but iPrimary_Flag = 0.
>
>For some reason, it looks to me what my SQL is wrong.
>

>select max(cClient_Account_Number) as cClient_Account_Number, ;
>			min(TR.cTrans_Relateds_Pk) as cTrans_Relateds_Pk, ;
>			count(*) as cntGuarantors from Trans inner join Trans_Relateds TR on ;
>			Trans.cTrans_pk = TR.cTrans_fk ;
>			where TR.iGuarantor_flag = 1 and TR.iPrimary_flag = 0 ;
>			group by Trans.cTrans_pk having cntGuarantors = 1 ;			
>			into cursor curProblems		

>
>Can you tell me, if I'm wrong and how should I re-write this SQL?
>
>Thanks a lot in advance.

This would work

		select cClient_Account_Number, ;
			cTrans_Relateds_Pk from Trans ;
			inner join Trans_Relateds TR on ;
			Trans.cTrans_pk = TR.cTrans_fk where cTrans_pk ;
			in (select cTrans_pk, count(*) as cntGuarantors ;
			from Trans inner join Trans_Relateds TR on ;
			Trans.cTrans_pk = TR.cTrans_fk ;
			group by Trans.cTrans_pk having cntGuarantors = 1) ;
		where TR.iGuarantor_flag = 1 and TR.iPrimary_flag = 0 ;			into cursor curProblems		

Do you have a better idea?