T-SQL: how to populate a column with a user department?

Question

I understand that this task should be quite simple, but for some reason I cannot understand it for some time. Finding such problems did not 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 to populate the DepartemntID column in the Documents table using the first (or any) user department located in the DepartmentsAndUsers table?

For example, User1 is in Dept1 and Dept3, so the DepartmentID for documents 1 and 4 must be populated with Dept1 or Dept3.

Please do not think about it from a normalization point, since I cannot change any table logic.

Any help or reference to some solution with the same scenario would be greatly appreciated.

Answer 1

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

Answer 2

:

UPDATE Documents SET DepartemntID = E.DepartemntID
FROM Documents D
LEFT JOIN (
    SELECT UserID, MIN(DepartemntID) DepartemntID
    FROM DepartmentsAndUsers
    GROUP BY UserID
) E ON E.UserID = D.AuthorID

:

DECLARE @Documents TABLE (
    DocumentID INT,
    AuthorID VARCHAR(16),
    DepartemntID VARCHAR(16)
)

DECLARE @DepartmentsAndUsers TABLE (
    DepartemntID VARCHAR(16),
    UserID VARCHAR(16)
)

INSERT @Documents VALUES
(1, 'User1', NULL),
(2, 'User2', NULL),
(3, 'User3', NULL),
(4, 'User1', NULL),
(5, 'User4', NULL)

INSERT @DepartmentsAndUsers VALUES
('Dept1', 'User1'),
('Dept1', 'User2'),
('Dept1', 'User3'),
('Dept2', 'User4'),
('Dept2', 'User5'),
('Dept3', 'User1'),
('Dept3', 'User3')

UPDATE @Documents SET DepartemntID = E.DepartemntID
FROM @Documents D
LEFT JOIN (
    SELECT UserID, MIN(DepartemntID) DepartemntID
    FROM @DepartmentsAndUsers
    GROUP BY UserID
) E ON E.UserID = D.AuthorID

SELECT * FROM @Documents