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:

Student	Course
Ahmed	Physic
Ahmed	Maths
Ahmed	Computer
Ahmed	Chemistry
Ali	Physic
Ali	Maths
John	Computer
John	Chemistry
King	Physic
King	Maths

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:

Manager	Staff
Ali	King
Ali	Mak
Mak	Sam
Sam	Jon

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!