This: -

AND (a3.ORDPROD LIKE 'OTHER%')

breaks your outer join and turns it into an inner join.

Try this: -

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

or this: -

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'

ah, too slow :hehe: