select mfr_id, mfr_name from tblManufacturers with (nolock) where mfr_id in ( select distinct oal.child_attrId from tbl_ORG_AttributeLinks oal with (nolock) inner join tbl_1FS_MappingQueue mq with (nolock) on mq.queueId = oal.mstr_attrId inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = mq.operatorOwnerId and opr.fsltablecode = mq.operatorOwnerType and opr.partnerId = mq.operatorId where oal.mstr_attrTypeId = 96 and oal.child_attrTypeId = 42 and mq.linkLevel = and nullif( mq.linkId, 0 ) is null and nullif( opr.fsl_choId, 0 ) is null and ( nullif( opr.address, '' ) is null or nullif( opr.city, '' ) is null or nullif( opr.state, '' ) is null or nullif( opr.zipcode, '' ) is null ) ) order by mfr_name