tblUsers --> 28 records
tblDepartmentsUsers --> something like a junction table between the above two tables.
I have entered several test records into tblDepartmentsUsers.
So that some of the users have Departments allocated to them.
I would like to have a query so that you can pass in a departmentID. The result should show all users in the users table. In addition, it should show the departmentID for the users that do have a department and null for the unacclocated users.
This the stored procedure that I have.
The problem is the where clause. because when I pass in the departmentID it returns only those allocated departments. Whereas I would like to see all users (null for users with unAllocated departmentID)
ALTER PROCEDURE [dbo].[DepartmentUsers_Get]
Users as u left join DepartmentsUsers as pu on u.UserID = du.UserID
du.DepartmentIDID = @DepartmentID