具有多个外键链接到同一主键的表

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.

最近我遇到了很多困难，我想用MySQL数据库获取查询结果——目前我还不确定问题是数据库还是实际查询。

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.

我希望查询数据库，以便返回匹配数据库中的属性，但返回的是玩家的名称，而不是他们的ID。

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.

但是当我加入第二个播放器时，p。名称已经映射到playerID1，因此它不能工作。

I suspect its the database which isn't designed very well, but any recommendations are welcome!

我怀疑它的数据库设计得不是很好，但任何建议都是欢迎的!

1 个解决方案

#1

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.

那个数据库设计让你头疼。您应该通过添加一个同时具有MatchID和PlayerID的MatchPlayer表来分解Match和Player之间的关系，从而允许尽可能多的玩家连接到一个匹配，而不必为每个玩家都提供一个字段。

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

这里演示SQLFiddle

#1