在一个连接的sql语句中选择多个同名的东西

6ojccjat 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(334)

我现在有一个sql语句，用一个学生的名字和他们在一个给定的学期里参加的运动的名字来填充一个表。运动的名称存储在运动表中，并带有运动id。然后运动id与选项表中的选项id相对应。然后将此选项id放入表student\u choices中标记为t1\u choice、t2\u choice和t3\u choice的每个术语的列中。如何从sql语句中获得t2\u选项和t3\u选项？谢谢你的帮助。
db小提琴链接

$stmt = $conn->prepare(
              "SELECT st.Name AS student, s.Name AS sport
              From Sports AS s INNER JOIN Choices AS c
              ON s.Sport_ID = c.Sport_ID INNER JOIN Student_Choices AS sc
              ON sc.T1_Choice = c.Choice_ID INNER JOIN Students AS st
              ON st.Username = sc.Username
              ");
            $stmt->execute();
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
              echo '<tr>
              <td>'.$row['student'].'</td>
              <td>'.$row['sport'].'</td>
              <td>'.$row['sport'].'</td>
              <td>'.$row['sport'].'</td>
              </tr>
              ';
            }

mysql Join pdo

来源：https://stackoverflow.com/questions/53227854/selecting-multiple-things-with-the-same-name-in-a-joined-sql-statement

1条答案

按热度按时间

kxkpmulp1#

解决方案是可以多次调用一个表的join。因为choices中的choice\u id需要链接到student\u choices表中的不同学期选项。因此，您需要使用不同的别名多次联接choices表，对于每个示例，您需要使用不同的别名联接sports表。我的解决方案是：

("SELECT st.Name AS student, T1.Name AS T1, T2.Name AS T2, T3.Name AS T3
              From Students AS st INNER JOIN Student_Choices AS sc
              ON st.Username = sc.Username
              INNER JOIN Choices AS c1
              ON sc.T1_Choice = c1.Choice_ID
              INNER JOIN Sports AS T1
              ON c1.Sport_ID = T1.Sport_ID
              INNER JOIN Choices AS c2
              ON sc.T2_Choice = c2.Choice_ID
              INNER JOIN Sports AS T2
              ON c2.Sport_ID = T2.Sport_ID
              INNER JOIN Choices AS c3
              ON sc.T3_Choice = c3.Choice_ID
              INNER JOIN Sports AS T3
              ON c3.Sport_ID = T3.Sport_ID
              ");

赞(0）回复(0）举报 2021-06-18

我来回答

在一个连接的sql语句中选择多个同名的东西

1条答案

相关问题

热门标签

最新问答