使用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

并希望得到以下结果:

**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屋！