Joining three tables using MySQL

Question

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

User · Answer

Don t join like that  It s a really really bad practice    It will slow down the performance in fetching with massive data  For example  if there were 100 rows in each tables  database server have to fetch 100x100x100   1000000 times  It had to fetch for 1 million times  To overcome that problem  join the first two table that can fetch result in minimum possible matching It s up to your database schema   Use that result in Subquery and then join it with the third table and fetch it  For the very first join --  100x100  10000 times and suppose we get 5 matching result  And then we join the third table with the result --  5x100   500  Total fetch   10000 500   10200 times only  And thus  the performance went up

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Just adding a point to previous answers that in MySQL we can either use table factor syntax   OR joined table syntax  mysql documentation Table factor example SELECT prd name  b name  FROM products prd  buyers b  Joined Table example SELECT prd name  b name  FROM products prd  left join buyers b on b bid   prd bid   FYI  Please ignore the fact the the left join on the joined table example doesnot make much sense  in reality we would use some sort of join table to link buyer to the product table instead of saving buyerID in product table

User · Answer

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

[mysql] Joining three tables using MySQL

Examples related to mysql