php - 如何根据多个表和值分隔来选择SQL结果

Question

表学生

---------------------------------------------
- student_id - student_name - student_image -
---------------------------------------------
-          1 - Ana          - avatar.jpg    -
-          2 - David        - avatar.jpg    -
-          3 - Jasmine      - avatar.jpg    -
---------------------------------------------

餐 table 类(class)

-------------------------------------------
- course_id - course_title - course_image -
-------------------------------------------
-         7 - Photoshop    - image.jpg    -
-         8 - Cinema 4D    - image.jpg    -
-         9 - Idesign      - image.jpg    -
-        10 - Illustrator  - image.jpg    -
-------------------------------------------

表学生_类(class)

--------------------------
- course_id - student_id - 
--------------------------
-         7 - 1   
-         8 - 2   
-         9 - 3     
-         9 - 2
-        10 - 3
-         8 - 1 
--------------------------

代码

<?php
$host_database = 'mysql:dbname=tablas;host=localhost';
$username = "root";
$password = "";

try{ $pdo = new PDO($host_database,$username,$password);; }
catch(PDOException $e){ echo 'Error al conectar: ' . $e->getMessage(); }

$sql = "SELECT * 
        FROM courses,students 
        WHERE course_id IN (
                            SELECT course_id 
                            FROM students_courses
                            ) 
        GROUP BY students.student_id";   

$statement = $pdo->prepare($sql);
$statement->execute();
$students = $statement->fetchAll(PDO::FETCH_ASSOC);
?>

<table>
<thead>
<th>ID Student</th>
<th>Name Student</th>
<th>Course Title</th>
</thead>

<?php foreach ($students as $valor) { ?>

<tr>
<td><?php echo $valor['student_id']; ?></td>
<td><?php echo $valor['student_name']; ?></td>
<td><?php echo $valor['course_title']; ?></td>
</tr>

<?php } ?>

</table>

结果:

+------------+--------------+--------------+| ID Student | Name Student | Course Title |+------------+--------------+--------------+|          1 | Ana          | Photoshop    ||          2 | David        | Photoshop    ||          3 | Jasmine      | Photoshop    ||          1 | Ana          | Indesign     ||          2 | David        | Indesign     ||          3 | Jasmine      | Indesign     ||          1 | Ana          | Illustrator  ||          2 | David        | Illustrator  ||          3 | Jasmine      | Illustrator  |+------------+--------------+--------------+
Like this result:
+------------+--------------+------------------------------+
| ID Student | Name Student |         Course Title         |
+------------+--------------+------------------------------+
|          1 | Ana          | Photoshop,Illustrator        |
|          2 | David        | Idesign,Cinema 4D            |
|          3 | Jasmine      | Photoshop,Cinema 4d,Indesign |
+------------+--------------+------------------------------+

Answer 1

在这种情况下您应该使用表连接。尝试这个 SQL 查询 -

SELECT students.student_id, student_name, GROUP_CONCAT(courses.course_title) as course_title
FROM students JOIN students_courses ON students_courses.student_id = students.student_id
JOIN courses ON courses.course_id = students_courses.course_id
GROUP BY students.student_id

更新:在此表架构中，NATURAL JOIN 也应该在 mySQL 中正常工作。

SELECT student_id, student_name, GROUP_CONCAT(course_title) as course_title
FROM students NATURAL JOIN students_courses NATURAL JOIN courses
GROUP BY students.student_id