with clientsRaw as ( select b.broker_Id as ownerId, 'BRO' as ownerType, b.broker_name as name from tblBrokers b with (nolock) where exists ( select * from tbl_EAI_inboundBatches e with (nolock) where e.ownerId= b.broker_Id and e.ownerType = 'BRO' ) union all select m.mfr_id as ownerId, 'MFR' as ownerType, m.mfr_name as name from tblManufacturers m with (nolock) where exists ( select * from tbl_EAI_inboundBatches e with (nolock) where e.ownerId= m.mfr_id and e.ownerType = 'MFR' ) union all select d.dstid as ownerId, 'DST' as ownerType, d.dstname as name from tbl_DST_Distributors d with (nolock) where exists ( select * from tbl_EAI_inboundBatches e with (nolock) where e.ownerId= d.dstId and e.ownerType = 'DST' ) union all select c.cho_id as ownerId, 'CHO' as ownerType, c.cho_name as name from tbl_CHO_Operators c with (nolock) where exists ( select * from tbl_EAI_inboundBatches e with (nolock) where e.ownerId= c.cho_id and e.ownerType = 'CHO' ) union all select f.userId as ownerId, 'MSC' as ownerType, f.company as name from tbl_MSC_FSLibraryUsers f with (nolock) where exists ( select * from tbl_EAI_inboundBatches e with (nolock) where e.ownerId= f.userId and e.ownerType = 'MSC' ) ) select *, convert( varchar, ownerId ) + ':' + ownerType as clientCode, name + '(' + ownerType + ')' as clientDisplay from clientsRaw where 1 = 2 order by name for json path, include_null_values