SELECT A.TLNANME,A.TLFullName,D.BRANCH_CODE,D.BRANCH_NAME,E.DEP_CODE,E.DEP_NAME,C.[Desc]
FROM TL_USER A
LEFT JOIN AbpUserRoles B ON A.ID = B.UserId
LEFT JOIN AbpRoles C ON B.RoleId = C.Id
LEFT JOIN CM_BRANCH D ON A.TLSUBBRID = D.BRANCH_ID
LEFT JOIN CM_DEPARTMENT E ON A.DEP_ID = E.DEP_ID
WHERE A.DEP_ID IS NOT NULL AND A.DEP_ID <> ''
ORDER BY D.BRANCH_ID,E.DEP_ID

SELECT A.TLNANME,A.TLFullName,D.BRANCH_CODE,D.BRANCH_NAME,E.DEP_CODE,E.DEP_NAME,C.[Desc]
FROM TL_USER A
LEFT JOIN AbpUserRoles B ON A.ID = B.UserId
LEFT JOIN AbpRoles C ON B.RoleId = C.Id
LEFT JOIN CM_BRANCH D ON A.TLSUBBRID = D.BRANCH_ID
LEFT JOIN CM_DEPARTMENT E ON A.DEP_ID = E.DEP_ID
WHERE A.DEP_ID IS NULL OR A.DEP_ID = ''
ORDER BY D.BRANCH_ID,E.DEP_ID