Select mcr.*

FROM dbo.G_MASTER_CODING_RESULTS AS mcr WITH (NOLOCK)

left join (

SELECT

MCRNUS.MASTER_CODING_RESULTS_ID

FROM G_MASTER_CODING_RESULTS AS MCRNUS With (NOLOCK)

WHERE MCRNUS.REVIEW_TYPE <> 'HCR'

AND (MCRNUS.C_V12_HCC = 96

OR MCRNUS.C_V22_HCC = 100

OR MCRNUS.C_V22_B_HCC = 100

OR MCRNUS.C_V22_C_HCC = 100)

) as S on mcr.MASTER_CODING_RESULTS_ID = S.MASTER_CODING_RESULTS_ID

WHERE S.MASTER_CODING_RESULTS_ID is null