How to Join Multiple Tables in MySQL to Retrieve Hierarchical and Relational Data?

Joining Multiple Tables in MySQL

Joining Student, Course, and Bridge Tables

To retrieve the names of students and the courses they have studied, we can join three tables: Student, Course, and Bridge. The Bridge table establishes the relationship between students and courses through the columns sid and cid, respectively.

Using ANSI syntax for clarity, the correct query to join these tables and retrieve the desired result is:

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;

Example Result:

Joining Employee and Manage Tables for Hierarchy

To retrieve a hierarchical structure of managers and staff, we can join the Employee and Manage tables using the columns id and eid, respectively. Here's the query for this:

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;

Example Result:

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