select a.ownerId, a.ownerType, a.alias, opr.operatorId, case when opr.oprCompanyType = '0' and opr.independentYN = 1 then 'I' when opr.oprCompanyType = '2' then 'H' when opr.oprCOmpanyType = '0' then 'U' when opr.oprCompanyType = '1' then 'P' else opr.oprCompanyType end oprCompanyType , opr.fsl_choId, opr.companyName, case when sg.operatorId is null then 0 when sg.parentGroupOperatorId is not null then 2 else 1 end subGroupLevel, p.companyName as parentGroup from tbl_IMPORT_Aliases a with (nolock) inner join tbl_OPR_CLientOperators opr with (nolock) on opr.operatorId = a.objectId and opr.ownerId= a.ownerId and opr.fsltablecode = a.ownerType left outer join tbl_OPR_SubGroups sg with (nolock) on sg.ownerId = opr.ownerId and sg.ownerType = opr.fsltablecode and sg.operatorId = opr.operatorId left outer join tbl_OPR_CLientOperators p with (nolock) on p.operatorId = sg.parentGroupOperatorId and p.ownerId = sg.ownerId and p.fsltablecode = sg.ownerType where a.ownerId = and a.ownerType = 'CHO' and a.objectType = 'operatorCompanyName' order by a.alias select sg.ownerId, sg.ownerType, 'zzzz' alias, opr.operatorId, case when opr.oprCompanyType = '0' and opr.independentYN = 1 then 'I' when opr.oprCompanyType = '2' then 'H' when opr.oprCOmpanyType = '0' then 'U' when opr.oprCompanyType = '1' then 'P' else opr.oprCompanyType end oprCompanyType , opr.fsl_choId, opr.companyName, case when sg.operatorId is null then 0 when sg.parentGroupOperatorId is not null then 2 else 1 end subGroupLevel, p.companyName as parentGroup from tbl_OPR_CLientOperators opr with (nolock) inner join tbl_OPR_SubGroups sg with (nolock) on sg.ownerId = opr.ownerId and sg.ownerType = opr.fsltablecode and sg.operatorId = opr.operatorId left outer join tbl_OPR_CLientOperators p with (nolock) on p.operatorId = sg.parentGroupOperatorId and p.ownerId = sg.ownerId and p.fsltablecode = sg.ownerType where opr.ownerId = and opr.fsltablecode = 'CHO' and opr.operatorId not in ( ) select #qmd_aliases.columnList# from qmd_aliases union all select #qmd_aliases.columnList# from qmd_noAlias order by alias, companyName