使用外键从第二个表获取值

Question

I am creating a fantasy football android app where getting the JSON data is done with php files and SQL. My problem involves 2 tables:

player_details

• player_id (primary key)
• first_name
• last_name

user_team (All id fields except PK are foreign keys linked to the player_id)

• user_team_id (primary key)
• goalkeeper_id
• rightback_id
• leftback_id
• etc.

I want to be able to run a PHP script, with a simple select statement in it, that will display a user team's details. I also want to include the first and last name of the players, which are linked to the foreign keys in the user_team position id fields. However all I can do is display the user_teams details and the players_id only.

SELECT *
FROM   user_team
WHERE  user_id = '".$uid."'

Answer 1

In this case, you have to do as many JOINs as the number of positions you have for your teams.

Like,

SELECT u.user_team_name, p1.goalkeeper_name, p2.rightback_name, p3.leftback_name, ...
FROM user_team u
   LEFT JOIN player_details p1 ON u.goalkeeper_id = p1.player_id
   LEFT JOIN player_details p2 ON u.rightback_id = p2.player_id
   LEFT JOIN player_details p3 ON u.leftback_id = p3.player_id
   ...
WHERE u.user_team_id = <some id>

Doable, but not really good.

Then, you may change your model, and introduce the third table, that ties the player with both team and position, like:

CREATE TABLE team_positions (
   player_id INT,
   team_id INT,
   position TEXT,

   FOREIGN KEY player_id
      REFERENCES player_details(player_id)

   FOREIGN KEY team_id
      REFERENCES user_team(user_team_id)
);

To increase the consitancy of data, it could be further normalized by introducing the positions table, than you going to refer it in team_positions by id too.

In the meantime, user_team table would retain just the team details, like name & any other stuff you wanna put there (emblem, perhaps?), along with team id.

This way, you're going to have quite flexible structure. And that is pretty much usual the way of doing stuff in relational DB model.