Table with multiple foreign keys linking to the same primary key

Question

I've been having a lot of difficultly recently getting the query result I want with my MySQL database - at the moment I'm not sure if the problem is with the database or the actual query. Basically its a football player database with these tables: ``` (player): player_id (primary), playerName (match): match_id (primary), playerID1, playerID2, playerID3, etc.. ``` I want to query the database so that I am returned with the attributes in the match database, but the names of the players are returned rather than their ID's. I can get it to work for one player, but not the others. Here's the code: ``` SELECT p.Name FROM `match` m inner join `player` p on p.player_id=m.playerID1 ``` But when I add the second player, `p.Name` is already mapped to `playerID1` so it won't work. I suspect its the database which isn't designed very well, but any recommendations are welcome!

Accepted Answer

That database design is causing your headaches. You should have decomposed the relationship between `Match` and `Player` by adding a `MatchPlayer` table that has both a `MatchID` and a `PlayerID`, thus allowing as many players as possible to be linked to a `Match`, without having to have a field for each one.

For your query though, you'll have to do something like this:

```
SELECT p.Name 
FROM `match` m
INNER JOIN `player` p 
    ON p.player_id IN (m.playerID1, m.playerID2, m.playerID3) //etc
```

Demo SQLFiddle HERE

Table with multiple foreign keys linking to the same primary key

Solution

Problem