Ok guys... if anyone else has the same problem... after much thinking, i came up with a cheater way to accompish what i wanted!!
here is the solution!
SELECT DISTINCT
a.field1, c.field2, a.field3,
MAX(left(d.field4,15)
)
FROM tbl_Table1 a
LEFT JOIN tbl_Table2_R c ON a.field1 = c.FKField1
LEFT JOIN tbl_Table3_R d ON a.Field1= d.FKField1
WHERE 1=1
AND a.field3= 'text'
GROUP BY a.field3, a.field1, c.field3
Just add max to all the fields that might be Many -> 1 with your primary table.
