select distinct m.MFR_ID, m.MFR_NAME from tblManufacturers m with (nolock) inner join tbl_SSO_Providers ssoProv with (nolock) on ssoProv.DomainOwnerID = m.MFR_ID and ssoProv.DomainOwnerType = 'MFR' order by m.MFR_NAME for json auto, include_null_values