php - 如何从一个表中选择数据并从另一个表中获取所有关联记录？

Question

我正在尽我所能简化我的问题，我希望我的解释能够阐明它的含义。

场景是这样的:

我有两个表:tbl_instructors 包含讲师 ID 和姓名等讲师信息，tbl_advisory 包含正在处理的类(class)的所有记录由特定的讲师，例如类(class)的 Course、Year 和 Section。所以我的 table 是这样的:

tbl_instructor:

id  | name
----+--------------
001 | Jhon Doe
002 | Kaitlyn Moore  

tbl_advisory:

instructor_id  | course        | year      |  section
---------------+---------------+-----------+-----------
001            | BSINT         | 2nd Year  | A
001            | BSINT         | 2nd Year  | C
001            | BSINT         | 2nd Year  | B
002            | BSBA          | 1st Year  | A
002            | BSBA          | 1st Year  | D

现在，我尝试在我的 PHP 查询中使用 INNER JOIN 选择讲师 ID 和姓名及其相关的咨询信息:

<?php
$getAdvisory = "SELECT 
  ti.id AS id,
  ti.name AS name,
  ta.course AS ccourse,
  ta.year AS cyear,
  ta.section AS csection
FROM tbl_instructor as ti
INNER JOIN tbl_advisory as ta
ON ti.id = ta.instructor_id";
OpenConn()->query($getAdvisory);

if ($getAdvisory->num_rows > 0) {
    while ($row = $getAdvisory->fetch_assoc()) {
     $id = $row['id'];
     $name = $row['name'];
    }
    ?>
    <td><?php echo $id; ?></td>
    <td><?php echo $name; ?></td>
    <td><button>View Advisory</button></td>
    <?php
 }
}
?>

这在我的 html/php 表中给出了结果集:

id   | name          |  action
-----+---------------+-----------
001  | John Doe      | View Advisory
001  | John Doe      | View Advisory
001  | John Doe      | View Advisory
002  | Kaitlyn Moore | View Advisory
002  | Kaitlyn Moore | View Advisory

我的问题是我希望我的 html 表与讲师 ID 不同，但仍返回每个讲师的所有相关咨询信息，因为我想在单击 时以模式显示讲师正在上的课每个讲师姓名旁边的查看咨询按钮，这样我可以在讲师删除或添加新咨询时更新我的​​表格。

我怎样才能实现它？提前致谢。

Answer 1

你需要 DISTINCT 子句

 SELECT DISTINCT 
  ti.id AS id,
  ti.name AS name,
  ta.course AS ccourse,
  ta.year AS cyear,
  ta.section AS csection
FROM tbl_instructor as ti
INNER JOIN tbl_advisory as ta ON ti.id = ta.instructor_id

这样你应该得到

id   | name          |  action
-----+---------------+-----------
001  | John Doe      | View Advisory
002  | Kaitlyn Moore | View Advisory

如果值在不同的行上，您可以使用(假的)聚合函数

SELECT ti.id AS id,
ti.name AS name,
min(ta.course) AS ccourse,
min(ta.year) AS cyear,
min(ta.section)  AS csection
FROM tbl_instructor as ti
INNER JOIN tbl_advisory as ta ON ti.id = ta.instructor_id
GROUP BY id, name