php - 一个表而不是另一个表中的 MySQL PHP ID

Question

| Fixture_ID | League_ID | Home_Team | Away_Team 
|          1 |         1 |         1 |         2 
|          2 |         1 |         2 |         3 
|          3 |         1 |         3 |         1 

| Result_ID | Fixture_ID | Home_Goals | Away_Goals 
|         1 |          1 |          2 |          0

| Team_ID | Team_Name   |
|       1 | Team A  
|       2 | Team B      
|       3 | Team C 

如何加入表格以仅显示尚未输入结果但在显示赛程(在下拉列表中)时输出实际团队名称(A 队对 B 队)的赛程？

以下代码适用于输出所有灯具:

echo '<td> <select name ="fixture_id">';    

// TRY TO SHOW FIXTURES WITH NO RESULTS
$stmt = $pdo->prepare('SELECT  f.*, t1.Team_Name AS Home, t2.Team_Name AS Away
                        FROM Fixture        f
                        INNER JOIN Team     t1 ON f.Home_Team = t1.Team_ID
                        INNER JOIN Team     t2 ON f.Away_Team = t2.Team_ID');


$stmt->execute();
foreach ($stmt as $row) {
    echo '<option>' . $row['Home'] . ' v ' .  $row['Away'] . '</option>';
}  

?>  

Answer 1

您的 SQL 应该如下所示:

SELECT  f.*, t1.Team_Name AS Home, t2.Team_Name AS Away
FROM Fixture        f
INNER JOIN Team     t1 ON f.Home_Team = t1.Team_ID
INNER JOIN Team     t2 ON f.Away_Team = t2.Team_ID
LEFT JOIN Result    r ON f.Fixture_ID = r.Fixture_ID
WHERE r.id IS NULL;