>>>>>>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.
Yes, I created the problem with the first select when I set the rtp_id = 0.
Here is some data from the TIP table.
TIP_ID RTP_ID ST_NAME
10.3 40.08 TIPProject1
12.03 42.03 TIPProject2
13.03 40.08 TIPProject3
In this table the RTP_ID serves the same function as the PARENTPROJECT field in RTP table. That is it identifies the parent (IN THE RTP table that the TIP project belongs to.
Here is some data from the RTP table.
RTP_ID PARENTPROJECT ST_NAME
40.08 blank RTPProject1
42.03 40.08 RTPProject2
10.06 40.08 RTPProject3
2.08 42.03 RTPProject4
Here is the result we need from the first select in order to make the second select work.
RTP_ID PARENTPROJECT ST_NAME
40.08 blank RTPProject1
42.03 40.08 RTPProject2
10.06 40.08 RTPProject3
2.08 42.03 RTPProject4
pseudoRTPid1 40.08 TIPProject1
pseudoRTPid2 42.03 TIPProject2
pseudoRTPid3 40.08 TIPProject3
The pseudoRTPid needs to be unique in the result cursor and must not duplicate any number that may exist as an RTP_ID in the RTP table. Hmmm . . . maybe I could use the tip_id + .001? I'll give that a try.
