Select RelatedColorNameEn,RelatedColorNo From TB_CodeRelatedColor Where RelatedColorNoIsUse = 1 and RelatedColorNameEn in ( select RelatedColor from ( select RelatedColor, (select count(*) from VW_Product As A Inner Join TB_CollectionColor As B On A.CodeProductColor = B.CodeProductColor Inner Join TB_Collection As C On B.CollectionNo = C.CollectionNo where CodeProductStatusNo = 1 And ProductStock > 0 and ColorIsUse = 1 and (p_type in (0)) and (B.codeProductColor = 'E591' ) and A.CodeProductColor = TB_CodeProductColor.CodeProductColor) as cnt from TB_CodeProductColor where ColorIsUse = 1 ) tempTable where cnt > 0 )
