sql - 通过引用索引跳转接收值

Question

我无法弄清楚如何从第三个表中获取值，通过引用索引从第一到第二到第三跳跃。数据：

          Players
Player_ID| Player_Name| ...   
    1    | Adam Smith | ...  
    2    | John  Doe  | ...

              Participants
Participant_ID| Event_ID | Player_ID| ...  
      1       |     1    |     2    | ... 
      2       |     1    |     1    | ...

                              Games
Game_ID|Event_ID|White_player (patricipant id)|Black_Player (participant_id)| ...  
   1   |    1   |             1               |                 2           | ...

我基本上想要的：

   Game_ID|Event_ID|White_player (patricipant id)|Black_Player (participant_id)| ...  
   1      |    1   |        John Doe             |            Adam Smith       | ...

但最后我需要这样的字符串：“John Doe vs Adam Smith”。我不想替换表中的值。我不知道我是否应该这样做，或者改变我的表格结构。

标签： sqloracle

Answer 1

经过一些修复后，我得到了我想要的结果：

select p1.player_name || ' vs ' || p2.player_name as competitors, g.game_id
from games g
     join participants_list wp
         on wp.event_id = g.event_id
         and wp.participant_id = g.white_player
     join players p1
         on wp.player_id= p1.player_id
     join participants_list bp
         on bp.event_id = g.event_id
         and bp.participant_id = g.black_player
     join players p2
         on bp.player_id = p2.player_id