Joindre trois tables en utilisant MySQL

J’ai trois tables nommées

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

Maintenant, pour montrer le nom de l’étudiant avec le nom du cours qu’il avait étudié comme,

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

Je construis la requête suivante

 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 

Mais il ne renvoie pas le résultat requirejs …

Et ce qui serait pour la forme normalisée, si je veux trouver qui est manager par rapport à d’autres:

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

Et veut obtenir ce résultat:

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

Utilisez simplement:

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

Utilisez la syntaxe ANSI et il sera beaucoup plus clair comment vous joignez les 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 

Pour normaliser la forme

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

Vous pouvez joindre plusieurs tables comme cet exemple ci-dessus.

Utilisez ceci:

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

Requête pour joindre plus de deux 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