mysql - 如何查询表 1 中的单行和表 2 中的多行？

Question

TABLE 1
student_id | name
-----------------
1          | A
2          | B
3          | C

TABLE 2
photo_id   | student_id | path
------------------------------
1          | 1          | /path/to/folder/apple.jpg
2          | 1          | /path/to/folder/orange.jpg
3          | 2          | /path/to/folder/cantaloupe.jpg
4          | 1          | /path/to/folder/lemon.jpg

public function studentPhotos($student_id) {
    $query = "SELECT table1.name,table2.path as photos FROM table1 INNER JOIN table2 ON table1.student_id = table2.student_id WHERE table1.student_id=?";
    $stmt = $this->con->prepare($query);
    $stmt->bind_param("i",$student_id);
    $stmt->execute();
    $stmt->bind_result($name,$photos);
    $student = array();
    while($stmt->fetch()){
        $temp = array(); 
        $temp['name'] = $name;
        $temp['photos'] = $photos;
        $student[] = $temp;
    }
    $stmt->close();
    return $student; 
}

结果:[{A,/path/to/folder/apple.jpg},{A,/path/to/folder/orange.jpg},{A,/path/to/folder/lemon.jpg} ]

期望的结果{A,/path/to/folder/apple.jpg,/path/to/folder/orange.jpg,/path/to/folder/lemon.jpg}

当然，我可以使用 PHP 更改它，但我说的是每个学生都有数百张照片。因此，所有学生的属性将与结果数组中的照片一样多。

有没有更好的查询方式？

Answer 1

你想在这里分组连接吗:

SELECT
    t1.name,
    COALESCE(GROUP_CONCAT(t2.path), 'NA') AS paths
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.student_id = t2.student_id
WHERE
    t1.student_id = ?;