with owners as ( select distinct opr.ownerId, opr.fsltablecode as ownerType from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = fsl_choId where opr.fsl_choIdDate is not null and opr.fsl_choId > 0 and opr.fsl_choIdBy > 0 and convert( datetime, convert( varchar, fsl_choIdDate, 101 )) >= and convert( datetime, convert( varchar, fsl_choIdDate, 101 )) < ), 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