with owners as ( select distinct mq.operatorOwnerId ownerId, mq.operatorOwnerType ownerType from tbl_1fs_MappingQueue mq with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = mq.operatorOwnerId and opr.fsltablecode = mq.operatorOwnerType and opr.operatorId = mq.operatorId where mq.linkLevel = and nullif( mq.linkId, 0 ) is null and nullif( opr.fsl_choId, 0 ) is null and mq.queueId in ( select distinct mstr_attrId from tbl_ORG_AttributeLinks with (nolock) where mstr_attrTypeId = 96 and child_attrTypeId = 42 and child_attrId = ) ), ownersFull 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' ) select ownerType, ownerType + ':' + cast( ownerId as varchar ) as ownerKey, ownerName from ownersFull order by ownerType, ownerName for json auto, include_null_values