SELECT distinct bg.cdr_dstName as BuyingGroup, bg.cdr_recordId as BuyingGroupId FROM tbl_CRM_Affiliations aff INNER JOIN tbl_CDR_Distributors cdr ON cdr.ownerID = AND cdr.fsl_tablecode = AND cdr.fsl_dstId = aff.partnerId INNER JOIN tbl_CDR_Distributors bg ON bg.ownerId = cdr.ownerID AND bg.fsl_tablecode = cdr.fsl_tablecode AND bg.cdr_dstCompanyType = 'G' AND bg.fslBuyingGroupId = aff.orgId WHERE aff.ownerId = 66 AND aff.ownerType = 'MSC' AND aff.affiliationType = 'BYG' AND aff.affiliationStatus = 'A' AND aff.orgType = 'BYG' FOR json path, include_null_values