>>>>>I have a table that contains a list of projects. Each project may be a parent, child, or grandchild project. One of the fields 'parentproject' identifies the relationships. If the field is populated with the ID of another project, then the referenced project is the current project's parent. I have a report with 2 groups to display these like this:
>>>>>
>>>>>Group1................Parent
>>>>>Group2.....................Child
>>>>>Details..........................Grandchild
>>>>>
>>>>>This report seems to work OK and is built on the following SELECT
>>>>>

>>>>>select r1.system, r1.rtp_id, trim(r1.st_name) + " / " + trim(r1.termini) + " / " + trim(r1.project) as st_name_a, r1.lead, r1.del1, ;
>>>>>	r2.rtp_id, trim(r2.st_name) + " / " + trim(r2.termini)  + " / " + trim(r2.project) as st_name_b, r2.lead, r2.del1 as del2, ;
>>>>>	r3.rtp_id, trim(r3.st_name) + " / " + trim(r3.termini)  + " / " + trim(r3.project) as st_name_c, r3.lead, r3.del1 as del3 ;
>>>>>	from rtp_projects r1 ;
>>>>>	left join rtp_projects r2 on r1.rtp_id = r2.parentproject ;
>>>>>	left join rtp_projects r3 on r2.rtp_id = r3.parentproject ;
>>>>>	where (r1.parentproject = 0 or isnull(r1.parentproject)) and ! isnull(r1.st_name) and ! isblank(r1.st_name);
>>>>>	order by r1.system, r1.st_name, r2.st_name, r3.st_name	

>>>>>
>>>>>Now comes my problem, there is a second table that also contains a list of projects that may be a child to any of the projects in the first table. I would like to include these projects in the report as well, either as a child, grandchild, or great-grandchild. I'm not clear on how the select should be modified to do this, and the report also. Do I need 2 or more detail bands or additional groups, a select with a UNION or more JOINS, etc?
>>>>
>>>>>
>>>>>Any pointers will be appreciated. Thanks.
>>>>
>>>>
>>>>Because you speak about more than one Detail band I assume you use VFP9.
>>>>
>>>>You can use UNION of course, but you could also use so called derived table.
>>>>But I would do this in two steps: First UNION both tables in ONE cursor and then using that cursor I'll do the main SELECT (that one you show us)
>>>
>>>Borislav -
>>>Thanks for your suggestion. It is simpler and more elegant than the solution I had cobbled together. However upon trying it, it fails because the cursor formed by the UNION has no data for the keyid (rtp_id) for the records coming from the TIP table. See below
>>>

>>>select system, tip.tip_id, 000.00 as rtp_id, rtp_id as parentproject, trim(st_name) + " / " + trim(termini) + " / " + trim(project) as st_name, lead, .f. as del1 ;
>>>from TIP ;
>>>inner join tipdatabase!proposed on tip.tip_id = proposed.tip_id ;
>>>where proposed.status = "Active" ;
>>>union ;
>>>select system, 000.00 as tip_id, rtp_id, parentproject, trim(st_name) + " / " + trim(termini) + " / " + trim(project) as st_name, lead, del1 ;
>>>from rtp_projects ;
>>>into cursor crsTemp
>>>
>>>select r1.system, r1.rtp_id, r1.st_name as st_name, r1.lead, r1.del1, ;
>>>	r2.tip_id, r2.rtp_id, r2.st_name as st_name, r2.lead, r2.del1 as del2, ;
>>>	r3.tip_id, r3.rtp_id, r3.st_name as st_name, r3.lead, r3.del1 as del3 ;
>>>	from crsTemp r1 ;
>>>	left join crsTemp r2 on r1.rtp_id = r2.parentproject ;
>>>	left join crsTemp r3 on r2.rtp_id = r3.parentproject ;
>>>	where (r1.parentproject = 0 or isnull(r1.parentproject)) and ! isnull(r1.st_name) ;
>>>	and ! isblank(r1.st_name);
>>>	order by r1.system, r1.st_name, r2.st_name, r3.st_name ;
>>>	into cursor crsRTP

>>>My solution of setting this value to 0 in the first SELECT creates duplicate keyids and spoils the second SELECT. I am thinking on a solution to this but so far I'm blank.
>>>:)
>>>
>>Just do not use UNION but UNION ALL. That way all dupicates will be presented in the new cursor.
>
>I think I wasn't clear. The problem is that there ARE duplicates in the result. All the records coming from the TIP table have 0.00 as their rtp_id which is supposed to be unique.

But you selected them as 0. Did TIP table has tip_id column?
BTW it will be easier to me to understand what you want if you post some example data from both tables and what you want as a result.