Hi Fabio,
Thanks, I will give the union a try.
the fieldlist and the where clause depend on user input.



>Hi Peter,
>
>----------
>now in the query if a claim hase for example 3 claimants I ned to have 3 lines with just the claimant info different
>if there are alose 3 carriers I need 3 more line containing the same claim info but with the 3 carrier names etc
>What I would like the best wourd be a treeview like grid
>
>here is part of the query method:
>
>

>SELECT &cFieldList,0000000 AS LINENO;
>FROM claims ;
>   LEFT JOIN links la ON la.parent_id=claim_id AND la.ltype="CLRE";
>   LEFT JOIN links lb ON lb.parent_id=claim_id AND lb.ltype="CLCA";
>   LEFT JOIN links lc ON lc.parent_id=claim_id AND lc.ltype="CLCT";
>   LEFT JOIN respond ON la.child_id=respond_id;
>	   LEFT JOIN carrier ON lb.child_id=carrier_id;
>	   LEFT JOIN claimnt ON lc.child_id=claimnt.claimnt_id;
>	   LEFT JOIN sys_user ON claims.analyst_id=sys_user.user_id;
>		LEFT JOIN statute ON lc.statute_id=statute.statute_id;
>	WHERE &lcWhere ;
>INTO CURSOR cc1 ;
>READWRITE
>

>------------------
>
>Your request is a union; a multi autojoin is very inefficient,
>and the result it depends by &cFieldList and &lcWhere, and i think you must add
>a grouping.
>
>try ( VFP not support complex subquery ) with a
>SELECT for "CLRE" and ( on VFP8 ) INSERT the rows for "CLCA" and "CLCT":
>

>SELECT &cFieldList,0000000 AS LINENO;
>FROM claims ;
>   LEFT JOIN links la ON la.parent_id=claim_id AND la.ltype="CLRE";
>   LEFT JOIN respond ON la.child_id=respond_id;
>   LEFT JOIN sys_user ON claims.analyst_id=sys_user.user_id;
>WHERE &lcWhere1 ;
>INTO CURSOR cc1 ;
>READWRITE
>
>INSERT INTO cc1 ;
>SELECT &cFieldList2,0000000 AS LINENO;
>FROM claims ;
>   LEFT JOIN links lb ON lb.parent_id=claim_id AND lb.ltype="CLCA";
>	   LEFT JOIN carrier ON lb.child_id=carrier_id;
>	   LEFT JOIN sys_user ON claims.analyst_id=sys_user.user_id;
>	WHERE &lcWhere2
>
>INSERT INTO cc1 ;
>SELECT &cFieldList3,0000000 AS LINENO;
>FROM claims ;
>   LEFT JOIN links lc ON lc.parent_id=claim_id AND lc.ltype="CLCT";
>	   LEFT JOIN claimnt ON lc.child_id=claimnt.claimnt_id;
>	   LEFT JOIN sys_user ON claims.analyst_id=sys_user.user_id;
>		LEFT JOIN statute ON lc.statute_id=statute.statute_id;
>	WHERE &lcWhere3
>

>
>INDEX on claim_id is not useful for the left join
>INDEX on links , child_id , user_id, statute_id can speed up a lot.
>
>Fabio