with mappedOperators as ( select opr.fsltablecode, opr.ownerId, opr.companyName, opr.operatorId, opr.fsl_choId, opr.address, opr.city, opr.state, opr.zipcode, opr.mfrCustNum, dst.dstId, dst.dstParentCo from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CDR_Distributors cdr with (nolock ) on cdr.cdr_recordId = opr.distrib1 and cdr.ownerId = opr.ownerId and cdr.fsl_tablecode = opr.fsltablecode inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where opr.fsltablecode = 'DST' and opr.fsl_choId > 0 and nullif( opr.mfrCustNum, '' ) is not null and opr.ownerId > 0 ) , unmappedOperators as ( select opr.fsltablecode, opr.ownerId, opr.companyName, opr.operatorId, opr.fsl_choId, opr.address, opr.city, opr.state, opr.zipcode, dd.accountNumber as mfrCustNum, dst.dstId, dst.dstParentCo from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_OPR_DistributionDetails dd with (nolock) on dd.operatorId = opr.operatorId and dd.ownerId =opr.ownerId and dd.ownerType = opr.fsltablecode inner join tbl_CDR_Distributors cdr with (nolock ) on cdr.cdr_recordId = dd.cdr_recordId and cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where opr.fsltablecode in ( 'MFR', 'BRO' ) and opr.ownerId > 0 and coalesce( opr.fsl_choId, 0 ) = 0 and nullif( dd.accountNumber, '' ) is not null ) select * from mappedOperators m where exists ( select * from unmappedOperators u where u.address = m.address and u.city = m.city and u.state = m.state and u.mfrCustNum = m.mfrCustNum and ( u.dstId = m.dstId or u.dstParentCo = m.dstParentCo ) )