Well.......................needed a tiny tweek but this seems to work

select IIF(ISNULL(T.PK), P.PK, T.PK) as PK, IIF(ISNULL(T.FK), P.FK, T.FK) AS FK, P.pNo, P.pDesc, T.tno, T.tDesc;
from Parts P full join Tools T on P.PK = T.PK and P.FK = T.FK into cursor csrToolsParts

select M1.*, TP.* from Main M1 LEFT JOIN csrToolsParts TP on M1.PK = TP.FK

(Unfortunately I may need to rethink the concept. The result is fine if sent to a spreadsheet (which we frequently do) but may not fit well with what I had in mind for the REPORT FORM.)

Thanks again for your help ..........Rich



>>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
>
>You may first try to do a FULL JOIN between Tools and Parts based on PK and FK and then
>

>select IIF(ISNULL(T.PK), P.PK, T.PK) as PK, IIF(ISNULL(T.FK), P.FK, T.FK), P.pNo, P.pDesc, T.tno, T.tDesc
>from Parts P full join Tools T on P.PK = T.PK and P.FK = T.FK into cursor csrToolsParts
>
>select M1.*, TP.* from Main M1 LEFT JOIN csrToolsParts TP on M1.PK = TP.PK