quick sql to show users manager

[sdmayhew]

I was updating all my sql scripts and found one of my favorites still works on 7.0 and 7.1 as long as dbo.Inv_Global_Windows_Users is populated

select ac.[name] ,ac.[last logon user],isnull([managerid],'No User ID')AS managerid
from dbo.Inv_AeX_AC_Identification ac
left outer join  (SELECT     u._ResourceGuid AS [user guid],
                    r.ChildResourceGuid AS [manager guid], u.UserId,  i.Name  AS managerid
                    FROM         dbo.ResourceAssociation r INNER JOIN 
                      dbo.vItem i ON r.ChildResourceGuid = i.Guid INNER JOIN  
                      dbo.Inv_Global_Windows_Users u ON r.ParentResourceGuid = u._ResourceGuid  
WHERE     (r.ResourceAssociationTypeGuid = '049C633F-8413-42AE-93EA-F4EB7EDAFC65'))v2
    on ac.[last logon user] = v2.[userid]

[sdmayhew]

If you want to be more accurate you can use primary user

 

--- Primary user to acidentify
select ac.[name],ac.[last logon user],Prime.[primary user] ,v2.[userid],v2.managerid,gugd1.*
from dbo.Inv_AeX_AC_Identification ac

left outer join (SELECT     _ResourceGuid, MAX([user]) AS [primary user]  
FROM         dbo.Inv_AeX_AC_Primary_User   
WHERE     ([User] IS NOT NULL) AND ([User] <> '')
GROUP BY _ResourceGuid  ) Prime                          
    on ac._resourceguid =  Prime._ResourceGuid

left outer join  (SELECT     u._ResourceGuid AS [user guid],
                    r.ChildResourceGuid AS [manager guid], u.UserId,  i.Name  AS managerid
                    FROM         dbo.ResourceAssociation r INNER JOIN 
                      dbo.vItem i ON r.ChildResourceGuid = i.Guid INNER JOIN  
                      dbo.Inv_Global_Windows_Users u ON r.ParentResourceGuid = u._ResourceGuid  
WHERE     (r.ResourceAssociationTypeGuid = '049C633F-8413-42AE-93EA-F4EB7EDAFC65'))v2
    on v2.[userid] =Prime.[primary user]
                            
left outer join inv_global_user_general_details gugd1                          
    on v2.[manager guid] = gugd1._resourceguid