引用相同列的两个外键
问题描述:
我有两个表:Livestream和Team。引用相同列的两个外键
媒体直播:
id_livestream int primary key
id_team1 int
id_team2 int
//我已经提到这两列到球队id_team列
团队:
id_team int primary key
name_team varchar(40)
image_team varchar(255)
我想从球队选择name_team对于Livestream中的两个参考列.. a我想示例如下:
id_team1 |团队名称| | team1的图片| id_team2 |团队名称2 | TEAM2的图像
答
您可以生成你想要通过简单地做两联接从Livestream
表到Team
表输出:
SELECT
lm.id_team1,
t1.name_team AS name_team_1,
t1.image_team AS image_team_1,
lm.id_team2,
t2.name_team AS name_team_2,
t2.image_team AS image_team_2
FROM Livestream lm
INNER JOIN Team t1
ON lm.id_team1 = t1.id_team
INNER JOIN Team t2
ON lm.id_team2 = t2.id_team
我这里假设出现在Livestream
每一支球队都会有一个条目某处Team
表。如果不是这种情况,并且您不希望NULL
值出现在结果集中,那么您可以切换到使用LEFT JOIN
以及COALESCE()
。
答
试试这个:
SELECT
ls.id_team1,
t1.name_team AS name_team_1,
t1.image_team AS image_team_1,
ls.id_team2,
t2.name_team AS name_team_2,
t2.image_team AS image_team_2
FROM Livestream ls
INNER JOIN Team t1
ON ls.id_team1 = t1.id_team
INNER JOIN Team t2
ON ls.id_team2 = t2.id_team
内与Team
的伎俩参加两次。
非常感谢@Tim它完美 –
我这样做,我不得不等待10分钟重新标记一个正确的答案:) 但是对不起,我要问的东西更多: 设s假设我有一个子表命名Livestream_details引用直播 我希望如果我自动删除Livestream中的一行以从Livestream_details中删除其引用 我应该在删除级联上使用哪个位置? –
这听起来像一个完全不同的问题,所以我不会在评论部分回答它。您可能想问一个新问题,但是“ON DELETE CASCADE”听起来像是一个选项。 –