嗨,伙计们,我的mySQL查询有问题。请参阅我的表格和当前查询。
表“教员”
---------------------------------------------
id |firstname | lastname | subject_id|
---------------------------------------------
1 Juan Dela Cruz 1
2 Antonio Brigada 2
--------------------------------------------
表“主题”
---------------
id | name |
---------------
1 English
2 Math
3 Science
--------------
我当前的查询
public function instructor(){
global $database;
$sql = "SELECT instructors.firstname as firstname, instructors.lastname as lastname ";
$sql .= "FROM subjects ";
$sql .= "JOIN instructors ";
$sql .= "WHERE subjects.id = instructors.subject_id";
$result = $database->query($sql);
while($instructor = $database->fetch_array($result)){
if (isset($instructor['firstname']) && isset($instructor['lastname'])){
return $instructor['lastname'] . " " . $instructor['firstname'];
} else {
return "N/A";
}
}
}
结果:
English Juan Dela Cruz
Math Juan Dela Cruz
Science Juan Dela Cruz
编辑
当前sql:
public function instructor(){
global $database;
$sql = "SELECT a.name, ";
$sql .= "COALESCE(CONCAT(b.firstname, ' ',b.lastname), 'N/A') as ins_name ";
$sql .= "FROM subjects a ";
$sql .= "LEFT JOIN instructors b ";
$sql .= "ON a.id = b.subject_id";
$result = $database->query($sql);
while($instructor = $database->fetch_array($result)){
return $instructor['ins_name'];
}
}
结果
English Juan Dela Cruz
Math Juan Dela Cruz
Science Juan Dela Cruz
应该是什么:
English Juan Dela Cruz
Math Antonio Brigada
Science N/A
调用讲师()函数
<?php foreach($subjects as $subject): ;?>
<tr class="subject_list">
<td><?php echo $subject->name; ?></td> // This will show all subjects
<td><?php echo $subject->instructor(); ?></td> // This will show instructor foreach subjects
</tr>
<?php endforeach; ?>
我希望有人能帮助我看到上面的结果。
最佳答案
LEFT JOIN
是你需要的
SELECT a.name,
COALESCE(CONCAT(b.firstName, ' ',b.LastName), 'N/A')
FROM subjects a
LEFT JOIN instructors b
ON a.ID = b.Subject_ID
SQLFiddle Demo