How to Join Multiple Tables in MySQL to Retrieve Related Data?

Joining Tables in MySQL

In MySQL, joining tables allows us to combine data from multiple tables based on shared columns. In this question, we aim to join three tables to retrieve specific information.

Joining Student, Course, and Bridge Tables

The given data includes three tables: Student (holds student names), Course (contains course names), and Bridge (connects student IDs to course IDs). To retrieve the desired result (listing student names with course names studied), you must use an inner join using ANSI syntax:

SELECT s.name AS Student, c.name AS Course
FROM student s
INNER JOIN bridge b ON s.id = b.sid
INNER JOIN course c ON b.cid = c.id
ORDER BY s.name;

This query will produce the expected result, showing each student's name and the courses they have studied.

Joining Employee and Manage Tables

For the second part of the question, we have two tables: Employee (holds employee names) and Manage (connects manager IDs to employee IDs). To retrieve the desired result (listing managers and their staff), you must use an inner join again:

SELECT e1.name AS Manager, e2.name AS Staff
FROM employee e1
INNER JOIN manage m ON e1.id = m.mid
INNER JOIN employee e2 ON m.eid = e2.id;

This query will provide the expected result, showing each manager's name and the names of their staff members.

The above is the detailed content of How to Join Multiple Tables in MySQL to Retrieve Related Data?. For more information, please follow other related articles on the PHP Chinese website!