T-SQL question
-
I have an application where I'm trying to retrieve a single dataset containing a user's department name and their business role. There are several instances where a user may not be assigned to a department; in those cases, I'm not getting any rows returned from my sproc although I still need to know the user's role. The pertinent code from my sproc is as follows:
SELECT
Roles.RoleDescription, Departments.DepartmentName
FROM Roles, UserRoles, Users, Departments, UserDepartments
WHERE Roles.RoleID = UserRoles.RoleID
AND Users.Username = 'aanderson' <-- example username
AND UserRoles.UserID = Users.UserID
AND UserDepartments.UserID = Users.UserID
AND Departments.DeptID = UserDepartments.DepartmentID
How do I go about creating a single T-SQL statement that will return either the user's role or their role and department?
-
Perform your joins in the FROM instead of the WHERE and use LEFT JOIN:
SELECT Roles.RoleDescription, Departments.DepartmentName
FROM Roles
INNER JOIN UserRoles
ON (Roles.RoleID = UserRoles.RoleID)
INNER JOIN Users
ON (UserRoles.UserID = Users.UserID)
LEFT JOIN UserDepartments
ON (UserDepartments.UserID = Users.UserID)
LEFT JOIN Departments
ON (Departments.DeptID = UserDepartments.DepartmentID)
WHERE Users.Username = 'aanderson' <-- example username
-
Thank you! Thank you! Thank you! You're a lifesaver!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply