Hi

This should help

SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]

FROM dbo.TABLE1 AS a1

INNER JOIN dbo.TABLE2 AS a2 ON a1.CUSTID = a2.CUSTID

LEFT OUTER JOIN dbo.TABLE3 AS a3 ON a1.CUSTID = a3.CUSTID AND (a3.ORDPROD LIKE 'OTHER%')

WHERE

(a1.CITY = 'THIS')

AND (a2.STATUS = 'THAT')

You don't need the TOP 100 PERCENT for this

You had the JOINS right however you WHERE clause turned the LEFT JOIN to an INNER join

Hope this helps

Andy