Hi Sergey
The common field is Fld2 in Table 1 and Fld4 in Table 2.
The net effect of what I am trying to achive, is that for each Row in Table 2 , I want a row for each Fld1 column in Table 1
So if I have 8 rows in Table 2 , and 2 unique Fld1 in Table 1, I want 8x2 rows in joined table (See my example below)


So say you have a Product File with 1000 rows (Table2) and a CustomerxProduct Sales in Table 1 (Has Customer Sales By product with say 50 customers, some may have all products or some products, I want to 'ALWAYS' end up with 1000 x 50 rows.
These rows will have the actual sales by Product/Csuotmer, or will have a Product/Customer but with a Blank/Zero/Null sale, but will always have ALL 1000 products

Regards,
Gerard





>What is relationship between Table1 and Table2 ?
>
>
>>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