MySQL left join using Multiple tables

There are three tables in total

student6

Student detils
( 5 records)

student_mark

Mark of each student of different exams

student_house

house name with house id
Two exams conducted each month and data is available for each student for three months in student_mark table ( 5 x 2 x 3 = 30 records )

You can download SQL Dump of these three tables here.

Average Mark of students in all exams

SELECT id,name,class,sex,student_id,mark FROM `student6` a LEFT JOIN (SELECT student_id, AVG(mark) as mark FROM student_mark GROUP BY student_id )  b   on a.id= b.student_id
idnameclasssexstudent
_id
mark
1John DeoFourfemale183.0000
2Max RuinThreemale277.1667
3ArnoldThreemale367.6667
4Krish StarFourfemale480.1667
5John MikeFourfemale585.5000

More on Average ( AVG ) Query.

Maximum mark of each student in all exams

SELECT id,name,class,sex,student_id,mark FROM `student6` a LEFT JOIN (SELECT student_id, MAX(mark) as mark FROM student_mark GROUP BY student_id )  b   on a.id= b.student_id
idnameclasssexstudent
_id
mark
1John DeoFourfemale193
2Max RuinThreemale287
3ArnoldThreemale380
4Krish StarFourfemale487
5John MikeFourfemale595

More on Group By Query.

Maximum mark of each month for all students

Out of two exams conducted every month, highest mark is considered.
SELECT id,name,class,sex,student_id,mark, month FROM `student6` a 
LEFT JOIN (SELECT student_id, MAX(mark) as mark,month(exam_dt) as month FROM student_mark GROUP BY month(exam_dt),student_id) b 
  on a.id= b.student_id
idnameclasssexstudent
_id
markmonth
1John DeoFourfemale1844
1John DeoFourfemale1935
1John DeoFourfemale1806
2Max RuinThreemale2804
2Max RuinThreemale2865
2Max RuinThreemale2876
3ArnoldThreemale3724
3ArnoldThreemale3805
3ArnoldThreemale3616
4Krish StarFourfemale4844
4Krish StarFourfemale4855
4Krish StarFourfemale4876
5John MikeFourfemale5954
5John MikeFourfemale5895
5John MikeFourfemale5956

More on Maximum ( MAX ) Query.

Average mark of each month for all students

SELECT id,name,class,sex,student_id,mark, month FROM `student6` a 
LEFT JOIN (SELECT student_id, AVG(mark) as mark,month(exam_dt) as month FROM student_mark GROUP BY month(exam_dt),student_id) b 
  on a.id= b.student_id
Output will be same table with change in data for Maximum column.

Consider mark of student for the exams conducted in later part of the month.

Here we will try by using maximum exam date for each month
 SELECT id,name,class,sex,mark,student_id,exam_dt FROM `student6` a 
LEFT JOIN (SELECT mark, student_id,exam_dt from student_mark where exam_dt in (
SELECT MAX(exam_dt) from student_mark group by month(exam_dt)) ) b   on a.id= b.student_id
idnameclasssexmarkstudent
_id
exam_dt
1John DeoFourfemale7612017-04-19
1John DeoFourfemale9012017-05-24
1John DeoFourfemale7512017-06-21
2Max RuinThreemale7522017-04-19
2Max RuinThreemale8622017-05-24
2Max RuinThreemale8722017-06-21
3ArnoldThreemale7232017-04-19
3ArnoldThreemale8032017-05-24
3ArnoldThreemale6132017-06-21
4Krish StarFourfemale8242017-04-19
4Krish StarFourfemale8542017-05-24
4Krish StarFourfemale8742017-06-21
5John MikeFourfemale9552017-04-19
5John MikeFourfemale8452017-05-24
5John MikeFourfemale8752017-06-21

LEFT JOIN three tables

Show maximum mark with student details and house name
SELECT a.id,a.name,a.class,b.mark,c.h_name FROM `student6` a 
LEFT JOIN (SELECT student_id, MAX(mark) as mark FROM student_mark GROUP BY student_id ) b  
 on a.id= b.student_id
LEFT JOIN student_house c on a.house_id=c.house_id 
idnameclassmarkh_name
1John DeoFour93Queen
2Max RuinThree87King
3ArnoldThree80King
4Krish StarFour87Jack
5John MikeFour95Queen

LEFT JOIN & ORDER BY


SELECT a.id,a.name,a.class,b.mark,c.h_name FROM `student6` a
LEFT JOIN 
(SELECT student_id, MAX(mark) as mark FROM student_mark 
GROUP BY student_id ) b
 on a.id= b.student_id 
 LEFT JOIN student_house c on a.house_id=c.house_id  
 order by h_name

idnameclassmarkh_name
4Krish StarFour87Jack
3ArnoldThree80King
2Max RuinThree87King
1John DeoFour93Queen
5John MikeFour95Queen

SQL Order By .

Maximum average Mark scored by using Order by and LIMIT

We will display list in the order of average value from maximum to minimum and then use LIMIT query to get first record.
SELECT id,name,class,sex,student_id,mark FROM `student6` a LEFT JOIN (SELECT student_id, AVG(mark) as mark FROM student_mark GROUP BY student_id )
  b   on a.id= b.student_id order by mark desc limit 0,1 
idnameclasssexstudent_idmark
5John MikeFourfemale585.5000
Visitors Rating
Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer