Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2006-12-27 : 05:51:37
|
| Hi,There are three tables:tblUsers --> 28 recordstblDepartmentstblDepartmentsUsers --> 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]@DepartmentID intASselect u.UserID, u.UserName, du.Percentagefrom Users as u left join DepartmentsUsers as pu on u.UserID = du.UserIDwhere du.DepartmentIDID = @DepartmentID |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-12-27 : 06:01:38
|
| Give this a go:selectu.UserID, u.UserName,du.PercentagefromUsers as u left join DepartmentsUsers as pu on u.UserID = du.UserIDanddu.DepartmentIDID = @DepartmentID--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2006-12-27 : 06:12:50
|
| Solved.Thanks |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
sql join