所以我需要连接5个表。其中一个我有问题。它有两列是不同表的相同外键，即
matchdetail
idmatch | idplayer1 | idplayer2 | time | (FK) idmatchnight (FK)
它是之间的连接表
player
(PK) idplayer | firstname | secondname | nationality
matchnight
(PK) idmatchnight | location
我的sql查询应该是什么？
我当前的代码：不工作
SELECT * FROM player
INNER JOIN coach ON player.idcoach = coach.idcoach
INNER JOIN sponsor ON player.idsponsor = sponsor.idsponsor
INNER JOIN matchdetail ON player.idplayer = matchdetail.idplayer1
AND player.idplayer = matchdetail.idplayer2
INNER JOIN matchnight ON matchdetail.idmatch
WHERE player.idplayer=:idplayer
;"
idmatch是一个代理主键，而不是复合密钥
最好重写您的查询：
SELECT ...
FROM matchdetail
INNER JOIN player AS p1 ON matchdetail.player1=p1.idplayer
INNER JOIN player AS p2 on matchdetail.player2=p2.idplayer
etc...
你目前的加入基本上只允许玩家自己比赛。通过两次加入player，您可以获得两个不同玩家的详细信息。
如果你想保留这个逻辑，你也可以使用OR而不是AND:
SELECT * FROM player
INNER JOIN coach ON player.idcoach = coach.idcoach
INNER JOIN sponsor ON player.idsponsor = sponsor.idsponsor
INNER JOIN matchdetail ON (player.idplayer = matchdetail.idplayer1
OR player.idplayer = matchdetail.idplayer2)
INNER JOIN matchnight ON matchdetail.idmatch
WHERE player.idplayer=:idplayer;
问题是，对于您当前的谓词（带AND），它基本上与相同
player.idplayer = matchdetail.idplayer1 = matchdetail.idplayer2
SELECT columns, you, actually, want
FROM matchnight m
JOIN matchdetail d
ON d.idmatchnight = m.idmatchnight
JOIN player p1
ON p1.idplayer = d.idplayer1
JOIN player p2
ON p2.idplayer = c.idplayer2;
感谢大家的回答
我自己找到了一个解决方案：）
SELECT * FROM matchdetail
INNER JOIN boxer ON matchdetail.idplayer1
AND matchdetail.idplayer2
INNER JOIN coach ON player.idcoach = coach.idcoach
INNER JOIN sponsor ON player.idsponsor = sponsor.idsponsor
INNER JOIN matchnight ON matchdetail.idmatchnight = matchnight.idmatchnight
WHERE player.idplayer