Joining three tables using MySQL

问题:

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

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 this result:

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

回答1:

Simply use:

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

回答2:

Use ANSI syntax and it will be a lot more clear how you are joining the tables:

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 

回答3:

For normalize form

select e1.name as \'Manager\', e2.name as \'Staff\'
from employee e1 
left join manage m on m.mid = e1.id
left join employee e2 on m.eid = e2.id

回答4:

SELECT *
FROM user u
JOIN user_clockits uc ON u.user_id=uc.user_id
JOIN clockits cl ON cl.clockits_id=uc.clockits_id
WHERE user_id = 158

回答5:

SELECT 
employees.id, 
CONCAT(employees.f_name,\" \",employees.l_name) AS   \'Full Name\', genders.gender_name AS \'Sex\', 
depts.dept_name AS \'Team Name\', 
pay_grades.pay_grade_name AS \'Band\', 
designations.designation_name AS \'Role\' 
FROM employees 
LEFT JOIN genders ON employees.gender_id = genders.id 
LEFT JOIN depts ON employees.dept_id = depts.id 
LEFT JOIN pay_grades ON employees.pay_grade_id = pay_grades.id 
LEFT JOIN designations ON employees.designation_id = designations.id 
ORDER BY employees.id;

You can JOIN multiple TABLES like this example above.

回答6:

Use this:

SELECT s.name AS Student, c.name AS Course 
FROM student s 
  LEFT JOIN (bridge b CROSS JOIN course c) 
    ON (s.id = b.sid AND b.cid = c.id);

回答7:

Query to join more than two tables:

SELECT ops.field_id, ops.option_id, ops.label
FROM engine4_user_fields_maps AS map 
JOIN engine4_user_fields_meta AS meta ON map.`child_id` = meta.field_id
JOIN engine4_user_fields_options AS ops ON map.child_id = ops.field_id 
WHERE map.option_id =39 AND meta.type LIKE \'outcomeresult\' LIMIT 0 , 30