PHP Ferrari PHP Ferrari - 5 months ago 16
SQL Question

MySql Join three tables

Hi I have three tables named

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


I build following query

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 result

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

Answer

why not simply:

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