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)
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