Thanks for the code but it doesn't seem to give the desired result.
For each PK in main the result should create N records where N = the largest number of matching records in Parts or Tools.
main.pk = 1 has 2 parts records and 1 tools records so there should be 2 records in the result for pk = 1
main.pk = 2 has 1 parts record and 3 tools records so there should be 3 records in the result for pk = 3
Your query gave 4 records for PK = 1 and 1 record for PK = 2 (and that record didn't find the matching tool record)
It's looking as if I may need to rethink the entire process. My result would be fine if we were to export as an Excel spreadsheet (something we do often) but developing a hard copy REPORT FORM may be problematic.
Thanks again........Rich
>Would this work for you?
>
>SELECT main.pk, main.date, main.desc, parts.pno, parts.pdesc, tools.tno, tools.tdesk
> FROM main
> LEFT JOIN parts ON parts.fk = main.pk
> LEFT JOIN tools ON tools.fk = parts.pk
>
>
>>I have a set of three table I would like to join (I intend to produce a report and want to have only one source table for the report). Here is a mock-up of the three tables
>>TABLE: MAIN
>>PK date desc.......................etc.
>>1 d1 desc1
>>2 d2 desc2
>>
>>TABLE parts
>>PK FK pno pdesc
>>1 1 p1 pdesc1
>>2 1 p2 pdesc2
>>3 2 p3 pdesc3
>>
>>TABLE tools
>>PK FK tno tdesc
>>1 1 t1 tdesc1
>>2 2 t1 tdesc1
>>3 2 t2 tdesc2
>>4 2 t3 tdesce
>>
>>I want the result to be
>>PK date desc pno pdesc tno tdesc
>>1 d1 desc1 p1 pdesc1 t1 tdesc1
>>1 d1 desc1 p2 pdesc2
>>2 d2 desc2 p3 pdesc2 t1 tdesc1
>>2 d2 desc2 t2 tdesc2
>>2 d2 desc2 t3 tdesc3
>>
>>Keeping in mind that this is VFP8..........
>>
>>I can, if necessary, get this result using xBase coding, but I'm hoping (even though I haven't figured it out) that there some SQL command (or sequence of commands) which could produce the desired result. (The repeated values for date and desc could be blank)
>>
>>Thanks for any and all suggestions...........Rich
