>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
If it's not broken, fix it until it is.
My Blog