Hi Sergey, Thanks for your reply.
This does not quite do it as with the Left Join I only end up with P1 to P8 once (i.e. 8 rows)
I want P1-P8 once for A1 and P1-P8 once for B1, so I want to end up with 16 rows

regards,
Gerard


>

>SELECT t2.Fld4, t1.fld1, t1.fld2, t1.fld3 FROM Table2 t2
>  LEFT JOIN table1 t1 ON t1.fld2 = t2.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