CLOSE DATABASES

CREATE CURSOR table1 (fld1 C(2), fld2 C(2), fld3 I)
INSERT INTO table1 VALUES ("A1", "P1", 1)
INSERT INTO table1 VALUES ("A1", "P2", 2)
INSERT INTO table1 VALUES ("B1", "P3", 3)
INSERT INTO table1 VALUES ("B1", "P4", 4)

CREATE CURSOR table2 (fld4 C(2))
INSERT INTO table2 VALUES ("P1")
INSERT INTO table2 VALUES ("P2")
INSERT INTO table2 VALUES ("P3")
INSERT INTO table2 VALUES ("P4")
INSERT INTO table2 VALUES ("P5")
INSERT INTO table2 VALUES ("P6")
INSERT INTO table2 VALUES ("P7")
INSERT INTO table2 VALUES ("P8")

SELECT DISTINCT fld1 FROM table1 INTO CURSOR dt2 NOFILTER

SELECT * FROM table2, dt2 INTO CURSOR td3

SELECT td3.*, t1.fld3 FROM td3 LEFT JOIN table1 t1 ON t1.fld1=td3.fld1 AND t1.fld2 = td3.fld4

>I have two tables that I am trying to join as below. Tried a few outer joins but they dont seem to do it
>
>TABLE 1
>-----------
>Fld1 Fld2 Fld3
>--- --- ----
>A1 P1 1
>A1 P2 2
>B1 P3 3
>B1 P4 4
>
>Table 2
>---------
>Fld4
>---
>P1
>P2
>P3
>P4
>P5
>P6
>P7
>P8
>
>
>After join, I want every row from Table 2 to be in the joined table with every row from Table 1, but want Fld2 to be in the Joined table also
>Fld4 Fld1 Fld2 Fld3
>--- ---- --- ---
>P1 A1 P1 1
>P2 A1 P2 2
>P3 A1
>P4 A1
>P5 A1
>P6 A1
>P7 A1
>P8 A1
>P1 B1
>P2 B1
>P3 B1 P3 3
>P4 B1 P4 4
>P5 B1
>P6 B1
>P7 B1
>P8 B1
>
>
>Tia
>Gerard