How to show nickname instead of foreign key?-CodePudding

play_by_play table I want to connect to multiple other tables so I can see the text instead of id. play_by_play table has three different columns that use team_id from another table.

When I run this query I get no results. No error, just no results. I get a few results when removing AND play_by_play.player3_team_id = team.team_id so I'm sure there's a correct way.

SELECT
    play_by_play.id,
    play_by_play.game_id,
    play_by_play.event_msg_action_type 
FROM play_by_play
    INNER JOIN event_message_type
        ON play_by_play.event_msg_type_id  = event_message_type.id
    INNER JOIN game
        ON game.game_id = play_by_play.game_id
    INNER JOIN team
        ON play_by_play.player1_team_id = team.team_id
        AND play_by_play.player2_team_id = team.team_id
        AND play_by_play.player3_team_id = team.team_id
LIMIT 500

Any resources to understand SQL syntax is appreciated. How to put these pieces in the correct order?

CodePudding user response：

You should use table team three times ... INNER JOIN team team1 ON play_by_play.player1_team_id = team1.team_id INNER JOIN team team2 ON play_by_play.player2_team_id = team2.team_id INNER JOIN team team3 AND play_by_play.player3_team_id = team3.team_id ...