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 11 Answers
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 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 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 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 join query with three tables and we want two values from the same column we set the alias name for every table in the joins. Same table name also declare as a different names.
const sql = `select p.ID,p.purchaseamount,urs.name as buyername,pd.productname, pd.amount,urs1.name as sellername from purchases p left join products pd on p.productid=pd.ID left join users urs on p.userid=urs.ID left join users urs1 on pd.userid=urs1.ID` 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 = 10500 times only. And thus, the performance went up!!!
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.
1Just adding a point to previous answers that in MySQL we can either use
table_factor syntax OR
joined_table syntax 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).
Query for three table join and limit set SELECT * FROM (SELECT t1.follower_userid, t2.*, t3.login_thumb, t3.login_name, t3.bio, t3.account_status, t3.gender FROM videos t2 LEFT JOIN follower t1 ON t1.follower_userid = t2.user_id LEFT JOIN videos_user t3 ON t1.follower_userid = t3.login_userid WHERE t1.following_userid='$userid' LIMIT $startpoint , $limit) AS ID ORDER BY ID DESC 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 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); 
