Hi Sergey
Thanks for that and it does exactly what I was trying to do.
I suspected it would not be possible with just one Select
Many thanks
Gerard



>

>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