eBA User’ın Tüm Yöneticilerini Sırasıyla Getiren Sorgu

Aşağıdaki sorgu 1000 sicilli kullanıcının tüm yönetici silsilesini getirir.

WITH O(USERID, USERNAME, MANAGERNAME, MANAGERUSERID, LVL)
AS (SELECT
m.USERID
,u.FIRSTNAME + ‘ ‘ + u.LASTNAME
,m.MANAGERUSERID
,u2.FIRSTNAME + ‘ ‘ + u2.LASTNAME
,1
FROM OSMANAGERS AS m
JOIN OSUSERS u ON u.ID = m.USERID
JOIN OSUSERS u2 ON u2.ID = m.MANAGERUSERID
WHERE u.STATUS = 1
AND u2.STATUS = 1 AND u.ID=’1000’
UNION ALL
SELECT
m.USERID
,u.FIRSTNAME + ‘ ‘ + u.LASTNAME
,m.MANAGERUSERID
,u2.FIRSTNAME + ‘ ‘ + u2.LASTNAME
,LVL + 1
FROM OSMANAGERS AS m
JOIN O AS o ON m.USERID = o.MANAGERNAME
JOIN OSUSERS u ON u.ID = m.USERID
JOIN OSUSERS u2 ON u2.ID = m.MANAGERUSERID
WHERE u.STATUS = 1
AND u2.STATUS = 1)
SELECT USERID, USERNAME, MANAGERNAME, MANAGERUSERID, LVL
FROM O
ORDER BY LVL DESC