T-SQL does not let you use use an alias you define in the select list anywhere else in the rest of the select statement except in ORDER BY clause. So perhaps one of these?SELECTLIST_ID,NAME,ADDRESS,(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWERFROM VIEW_LISTWHERE (SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) = 'No'
OrSELECT * FROM (SELECTLIST_ID,NAME,ADDRESS,(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWERFROM VIEW_LIST)as sWHERE ANSWER = 'No'
Alternatively, you could rewrite the query to join the VALID_ANSWER and VIEW_LIST in the from clause and then apply the where clause