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 |
jamie_pattison
Yak Posting Veteran
65 Posts |
Posted - 2013-04-03 : 10:58:25
|
I have a select query which also has a sub query that im having problems with. My table for the Users structure isIDDepartmentNameTeamLeaderIDData isID Department Name TeamLeaderID1 IT Tom NULL2 Sheila 13 Ben 14 ADMIN Tony NULLIf the TeamleaderID is NULL it means they are a teamLeader.If the TeamLeaderID has a value it means they are part of that IDs team.For all child records the department is left blank and is retrieved from their TeamLeaderIDSo in this case it means Shelia and Ben are in Tom's team (their TeamleaderID is 1 and ID 1 is Tom).Here is my SELECT querySELECT Department, Name FROM UsersThis is nice and easy but now i need the departments from another table that has the userID saved against the record (ID from the above example), so my SQL is now likeSELECT col1, col2, (SELECT Department FROM Users where ID =(CASE WHEN TeamLeaderID IS NULL THEN MyTable.ID ELSE MyTable.ID END)) As DepartmentNameFROM MyTableWhat i am trying to return from my select is the department for every user. So if the TeamLeaderID is NULL then i want it to use the records ID (MyTable.ID in this case) to get the department name, otherwise if the myTable.ID has a value then to use that value (remember NULL means its a team leader and a value means its a member of the team, therefore in simple english i am getting the department name from the users name where the ID (If null would use the current records ID otherwise if it has a value it would use that value)Could anyone help?Thanks |
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-03 : 11:05:33
|
this what you're after?SELECT a.name, isnull(a.department, b.department)FROM Users aLEFT JOIN Users bOn a.TeamLeaderID = b.id; |
|
|
jamie_pattison
Yak Posting Veteran
65 Posts |
Posted - 2013-04-03 : 11:42:30
|
Im not sure if thats what im after. I was hoping for a SQL Sub Query as i posted.I have also tried the below syntax which again gives me the same problem(SELECT Department FROM Users where ID = (SELECT ISNULL(TeamLeaderID, myTable.ID) As ID WHERE (ID=MyTable.ID)))The problem i have here is that the department names for all child records are blank |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-03 : 12:51:04
|
Did you try running the code I posted? |
|
|
|
|
|