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

AND (a3.ORDPROD LIKE 'OTHER%')

SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]
FROM dbo.TABLE1 a1 
INNER JOIN dbo.TABLE2 a2 ON a1.CUSTID = a2.CUSTID 
LEFT OUTER JOIN (SELECT CUSTID, ORDPROD 
                 FROM dbo.TABLE3 
                 WHERE ORDPROD LIKE 'OTHER%') a3 ON a1.CUSTID = a3.CUSTID
WHERE a1.CITY = 'THIS' AND a2.STATUS = 'THAT';

SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]
FROM dbo.TABLE1 a1 
INNER JOIN dbo.TABLE2 a2 ON a1.CUSTID = a2.CUSTID 
LEFT OUTER JOIN dbo.TABLE3 a3 ON a1.CUSTID = a3.CUSTID AND a3.ORDPROD LIKE 'OTHER%'
WHERE a1.CITY = 'THIS' AND a2.STATUS = 'THAT'