>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