使用 MySQL 连接三个表 [英] Joining three tables using MySQL

问题描述

我有三个名为的表

**Student Table**
-------------
id    name
-------------
1     ali
2     ahmed
3     john
4     king

**Course Table**
-------------
id    name
-------------
1     physic
2     maths
3     computer
4     chemistry

**Bridge**
-------------
sid    cid
-------------
1     1
1     2
1     3
1     4
2     1
2     2
3     3
3     4
4     1
4     2

现在显示学生姓名和他学习的课程名称，

Now to show the student name with the course name which he had studied like,

**Result**
---------------------------
Student        Course
---------------------------
ahmed         physic
ahmed         maths
ahmed         computer
ahmed         chemistry
ali           physic
ali           maths
john          computer
john          chemistry
king          physic
king          maths

我构建以下查询

select s.name as Student, c.name as Course from student s, course c join bridge b on c.id = b.cid order by s.name

但它没有返回所需的结果...

But it does not return the required result...

如果我想找到谁是经理而不是其他人，那么规范化形式会是什么:

And what would be for normalized form, if I want to find who is manager over other:

**employee**
-------------------
id        name
-------------------
1         ali
2         king
3         mak
4         sam
5         jon

**manage**
--------------
mid      eid
--------------
1         2
1         3
3         4
4         5

并且想要得到这个结果:

And wants to get this result:

**result**
--------------------
Manager      Staff
--------------------
ali          king
ali          mak
mak          sam
sam          jon

推荐答案

简单使用:

select s.name "Student", c.name "Course"
from student s, bridge b, course c
where b.sid = s.sid and b.cid = c.cid 

这篇关于使用 MySQL 连接三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！