with owners as ( select distinct mq.operatorOwnerId ownerId, mq.operatorOwnerType ownerType from tbl_1fs_MappingQueue mq with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = mq.operatorOwnerId and cdr.fsl_tablecode = mq.operatorOwnerType and cdr.cdr_recordId = mq.cdr_recordId where mq.linkLevel = and nullif( mq.linkId, 0 ) is null and nullif( cdr.fsl_dstId, 0 ) is null --and mq.cdr_recordId > 0 and nullif( mq.cdr_recordId, 0 ) is not null and nullif( mq.operatorId, 0 ) is null ), ownersByType as ( select o.*, cho.cho_name as ownerName from tbl_CHO_Operators cho with (nolock) inner join owners o on o.ownerId = cho.cho_id and o.ownerType = 'CHO' union all select o.*, dst.dstName as ownerName from tbl_DST_Distributors dst with (nolock) inner join owners o on o.ownerId = dst.dstId and o.ownerType = 'DST' union all select o.*, mfr.mfr_name as ownerName from tblManufacturers mfr with (nolock) inner join owners o on o.ownerId = mfr.mfr_id and o.ownerType = 'MFR' union all select o.*, bro.broker_name as ownerName from tblBrokers bro with (nolock) inner join owners o on o.ownerId = bro.broker_id and o.ownerType = 'BRO' union all select o.*, msc.company as ownerName from tbl_MSC_FSLibraryUsers msc with (nolock) inner join owners o on o.ownerId = msc.userId and o.ownerType = 'MSC' ), ownersFull as ( select ownerType, ownerType + ':' + cast( ownerId as varchar ) as ownerKey, ownerName from ownersByType ) #forJSON( 'ownersFull', attributes.datastore, 'ownerType, ownerName' )#