4つのテーブルをJOINするときの良い方法を教えてほしいです

Question

前提

予約システムを作成しています。
以下のデータがあるとき、一つの予約IDに紐づく各担当者を表示したいです。

予約テーブル

予約ID	日付
1	2022-11-01
2	2022-11-02

担当者テーブル

予約ID	担当者ID	役割コード
1	1	10
1	2	20
1	3	30

ユーザーテーブル

ユーザID	担当者姓	担当者名
1	山田	太郎
2	田中	次郎
3	高橋	三郎

役割コードテーブル

役割コード	役割名
10	料理
20	写真
30	演出

実現したいこと

SQLで以下のようなデータを取得したいです。

予約一覧

予約ID	日付	担当者（料理）	担当者（写真）	担当者（演出）
1	2022-11-01	山田太郎	田中次郎	高橋三郎

該当のソースコード

SELECT
　予約ID,
　日付,
　CONCAT(ユーザー姓,ユーザー名) AS '担当者(料理）',
　CONCAT(ユーザー姓,ユーザー名) AS '担当者(写真）',
　CONCAT(ユーザー姓,ユーザー名) AS '担当者(演出）',
　FROM　予約
　INNER JOIN
　(
　　SELECT
　　　予約.予約ID,
　　　CONCAT(ユーザー姓,ユーザー名) AS '担当者'
　　　FROM　担当者
　　　INNER JOIN　ON
　　　　担当者.ユーザID = ユーザー.ユーザID
　)　AS 担当者ALL　ON
　予約.予約ID = 担当者.予約ID
　　　
　

※上記はイメージです。実際には動きません。

わからないこと

予約テーブルにJoinする対象をサブクエリで取得しますが、その中で各担当者を分けてJOINする方法がわかりません。
WHERE句で役割コード = 10のときは料理の担当者、20のときは写真の担当者…とするにはどのようにしたらよいでしょうか？

補足情報（FW/ツールのバージョンなど）

・SQL Server Management Studio v18.12.1
・Windows10

Comments

[SurferOnWww]

タグの MySQL は何か関係あるのですか？　「SQL Server Management Studio v18.12.1」と書いてあるので SQL Server の質問ですよね。関係なければ MySQL のタグは外してください。関係あるならどのように関係するのか書いてください。

[marokatsu]

コメントありがとうございます。MYSQLのタグを削除しました。

[SurferOnWww]

「該当のソースコード」は動くのですか？　そうは見えないのですが・・・　動くのであれば、実行結果はどうなって、それと質問者さんが期待する結果とどう異なるのか書けませんか？

[marokatsu]

該当ソースコードはイメージとなります。実際には動きません。
動かないことを追記しました、

Answer 1

サブクエリは必要なさそうなので普通に冗長にjoinすればよいでしょう

Comments

[marokatsu]

ありがとうございます。
一つ一つJOINしてうまくできました！