SELECT DISTINCT cm.CASE_NO
FROM Case_Main cm
/*
INNER JOIN Case_Actions ca
ON cm.CASE_NO = ca.CASE_NO
*/
INNER JOIN Case_Attachments catt
ON cm.CASE_NO = catt.CASE_NO
WHERE cm.STARTED >= '2015-06-01'
AND cm.STARTED <= '2015-06-30'
AND catt.DESCRIPTION LIKE '%nlto%'
and cm.STATUS <> 'UNFOUNDED'
;
Note, there is no apparent reason for including the table Case_Actions in the query as you are only filtering on fields in Case_Main & Case_Attachments
When you execute the query in SSMS, does return an error message? If yes, copy-paste it into this question, and click on the error message, watch the cursor jump to the offending line, and tell us what line that is.
btw That's a wompload of nested subqueries in the WHERE clause you have there.