SQL - Update multiple fields per row from the same lookup table without multiple joins

I have a temp table similar to this

create table #temp
(
    log_id int,
    ...some other fields,
    one_user_id int,
    two_user_id int,
    three_user_id int,
    four_user_id int,
    one_username varchar(50),
    two_username varchar(50),
    three_username varchar(50),
    four_username varchar(50)
)

I start out knowing all the user ids, but then I need to look up their names in a user lookup table and update the name fields in the temp table.

create table #user_lookup
(
    user_id int,
    username varchar(50)
)

I know I could join to the user lookup table once for every id using a different alias to get them all, but I was looking for a slick way to do it just once.

Any ideas ?

EDIT:

Ok, more info on the purpose for multiple users per row. The #temp table row (not all fields displayed) signifies a log entry that represents a collation of multiple actions by potentially multiple users, but all tying to that on开发者_StackOverflow社区e log row.

I could have duplicate log rows, one for each user who played a role, but it's easier to consume on the client side as single rows.

This is why there are multiple users per row.

---

I think this should work:

UPDATE temp
   SET one_username = u1.username
     , two_username = u2.username
     , three_username = u3.username
     , four_username = u4.username
  FROM #temp as temp
  join #user_lookup as u1 on u1.user_id = temp.one_user_id
  join #user_lookup as u2 on u2.user_id = temp.two_user_id
  join #user_lookup as u3 on u3.user_id = temp.three_user_id
  join #user_lookup as u4 on u4.user_id = temp.four_user_id

But I don't know why you have four users in one table... ;)

---

The only other real alternative solution is to pull in the related records with an IN clause and make use of CASE statements to tie the usernames with the correct user_id's. However this is way more complicated than simply using a JOIN statement and doesn't really offer any advantage except that there aren't multiple JOIN's involved. Here is a complete working sample of how to pull data using this structure:

create table #temp
(
    one_user_id int,
    two_user_id int,
    three_user_id int,
    four_user_id int,
    one_username varchar(50),
    two_username varchar(50),
    three_username varchar(50),
    four_username varchar(50)
)
insert #temp (one_user_id, two_user_id, three_user_id, four_user_id) values (1, 3, 6, 7)
insert #temp (one_user_id, two_user_id, three_user_id, four_user_id) values (2, 5, 8, 1)

;with User_Lookup as (
    select 1 as user_id, 'abc' as username union
    select 2, 'def' union
    select 3, 'ghi' union
    select 4, 'jkl' union
    select 5, 'mno' union
    select 6, 'pqr' union
    select 7, 'stu' union
    select 8, 'vwx' union
    select 9, 'jon' union
    select 10, 'bob'
), Result as (
    select
        one_user_id,
        two_user_id,
        three_user_id,
        four_user_id,
        max(case when U.user_id = one_user_id then U.username end) as one_username,
        max(case when U.user_id = two_user_id then U.username end) as two_username,
        max(case when U.user_id = three_user_id then U.username end) as three_username,
        max(case when U.user_id = four_user_id then U.username end) as four_username
    from
        #Temp T,
        User_Lookup U
    where
        U.user_id in (T.one_user_id, T.two_user_id, T.three_user_id, T.four_user_id)
    group by
        T.one_user_id, T.two_user_id, T.three_user_id, T.four_user_id
)
update
    #temp
set
    one_username = R.one_username,
    two_username = R.two_username,
    three_username = R.three_username,
    four_username = R.four_username
from
    Result R
inner join
    #temp T on R.one_user_id=T.one_user_id and R.two_user_id=T.two_user_id
        and R.three_user_id=T.three_user_id and R.four_user_id=T.four_user_id

select * from #temp

drop table #temp

Output:

one_user_id two_user_id three_user_id   four_user_id    one_username    two_username    three_username  four_username
1           3           6               7               abc             ghi             pqr             stu
2           5           8               1               def             mno             vwx             abc