Thanks Sergey
I'll give that a try
Regards,
Gerard

>Why not?
>

>SELECT dt2.fld1, t2.fld4, t1.fld3   ;
>	FROM table2 t2 ;
>	JOIN (SELECT DISTINCT fld1 FROM table1) dt2 ON .T. ;
>	LEFT JOIN table1 t1 ON t1.fld1=dt2.fld1 AND t1.fld2 = t2.fld4
>

>
>>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