我正在尝试将left join
我的两个表放在一起,并且仅在之后使用一个条件时才起作用,ON
但是当我尝试添加第二个条件时,我没有从firstname
和lastname
列中获得任何信息。我一直在这里查看其他答案,从我看到的结果来看,我所做的一切都正确,因此我无法弄清为什么它不起作用。
这是我的尝试:
$stmt = 'SELECT courses.*, teachers.firstname, teachers.lastname ';
$stmt .= 'FROM courses LEFT JOIN teachers ON ';
$stmt .= 'courses.main_teacher = teachers.id AND courses.secondary_teacher = teachers.id';
$prep_stmt = $db->prepare($stmt);
$prep_stmt->execute();
while ($db_row = $prep_stmt->fetch(PDO::FETCH_ASSOC)) {
echo '<tr>';
echo '<td class="info-cell">' . $db_row['name'] . '</td>';
echo '<td class="info-cell">' . $db_row['curriculum_url'] . '</td>';
echo '<td class="info-cell">' . $db_row['rating_criteria'] . '</td>';
echo '<td class="info-cell">' . $db_row['firstname'] . ' ' . $db_row['lastname'] . '</td>'; //Echoes main_teacher
echo '<td class="info-cell">' . $db_row['firstname'] . ' ' . $db_row['lastname'] . '</td>'; //Echoes secondary_teacher
echo '<td class="info-cell">' . $db_row['for_programme'] . '</td>';
echo '<td class="info-cell">
<button class="small-btns"><img src="../includes/img/edit.png"></button>
</td>';
echo '<td class="info-cell">
<button class="small-btns"><img src="../includes/img/delete.png"></button>
</td>';
echo '</tr>';
}
这是它的输出:
如您所见,使用多个条件时,它不会从教师表中返回任何结果,这是为什么呢?
编辑:
我数据库中的课程表:
我数据库中的Teachers表:
我想要的理想结果:
看来您需要加入teachers
2次
select
c.*,
t1.firstname as main_teacher_firstname,
t1.lastname as main_teacher_lastname,
t2.firstname as secondary_teacher_firstname,
t2.lastname as secondary_teacher_lastname
from courses c
left join teacher t1 on t1.id = c.main_teacher
left join teacher t2 on t2.id = c.secondary_teacher
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句