Igor Nikiforov Igor Nikiforov - 16 days ago 5
SQL Question

T-SQL: How to fill a column with user's department?

I realize that this task should be pretty simple but for some reason I can't figure it out for some time. Search for similar problems didn't help either.

So, let's say I have two tables: Documents and DepartmentsAndUsers.

--- Documents --- (one document could have any user as its author)
DocumentID AuthorID DepartemntID
1 User1 null
2 User2 null
3 User3 null
4 User1 null
5 User4 null

--- DepartmentsAndUsers --- (a user could be in any number of departments)
DepartmentID UserID
Dept1 User1
Dept1 User2
Dept1 User3
Dept2 User4
Dept2 User5
Dept3 User1
Dept3 User3


How do I fill DepartemntID column in Documents table with first (or any) of user's departments located in DepartmentsAndUsers table?

For example, User1 is in Dept1 and in Dept3 so DepartmentID for Documents 1 and 4 should be filled with Dept1 or Dept3 values.

Please don't think on this from the normalization point as I am unable to change any logic of the tables.

Any help or link to some solved solution with the same scenario would be highly appreciated.

Answer
with cte as( 
    select 
        UserID,
        max(DepartmentID) as DepartmentID
    from DepartmentsAndUsers
    group by UserID)

select 
    d.DocumentID,
    d.AuthorID,
    cte.DepartmentID
from Documents d
inner join cte on cte.UserID = d.AuthorID
Comments