Examples with Multiple table left join using group by , max ,avg,order by query

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 )
LEFT JOIN ( Basic Query)
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

id	name	class	sex	student _id	mark
1	John Deo	Four	female	1	83.0000
2	Max Ruin	Three	male	2	77.1667
3	Arnold	Three	male	3	67.6667
4	Krish Star	Four	female	4	80.1667
5	John Mike	Four	female	5	85.5000

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

id	name	class	sex	student _id	mark
1	John Deo	Four	female	1	93
2	Max Ruin	Three	male	2	87
3	Arnold	Three	male	3	80
4	Krish Star	Four	female	4	87
5	John Mike	Four	female	5	95

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

id	name	class	sex	student _id	mark	month
1	John Deo	Four	female	1	84	4
1	John Deo	Four	female	1	93	5
1	John Deo	Four	female	1	80	6
2	Max Ruin	Three	male	2	80	4
2	Max Ruin	Three	male	2	86	5
2	Max Ruin	Three	male	2	87	6
3	Arnold	Three	male	3	72	4
3	Arnold	Three	male	3	80	5
3	Arnold	Three	male	3	61	6
4	Krish Star	Four	female	4	84	4
4	Krish Star	Four	female	4	85	5
4	Krish Star	Four	female	4	87	6
5	John Mike	Four	female	5	95	4
5	John Mike	Four	female	5	89	5
5	John Mike	Four	female	5	95	6

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

id	name	class	sex	mark	student _id	exam_dt
1	John Deo	Four	female	76	1	2017-04-19
1	John Deo	Four	female	90	1	2017-05-24
1	John Deo	Four	female	75	1	2017-06-21
2	Max Ruin	Three	male	75	2	2017-04-19
2	Max Ruin	Three	male	86	2	2017-05-24
2	Max Ruin	Three	male	87	2	2017-06-21
3	Arnold	Three	male	72	3	2017-04-19
3	Arnold	Three	male	80	3	2017-05-24
3	Arnold	Three	male	61	3	2017-06-21
4	Krish Star	Four	female	82	4	2017-04-19
4	Krish Star	Four	female	85	4	2017-05-24
4	Krish Star	Four	female	87	4	2017-06-21
5	John Mike	Four	female	95	5	2017-04-19
5	John Mike	Four	female	84	5	2017-05-24
5	John Mike	Four	female	87	5	2017-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

id	name	class	mark	h_name
1	John Deo	Four	93	Queen
2	Max Ruin	Three	87	King
3	Arnold	Three	80	King
4	Krish Star	Four	87	Jack
5	John Mike	Four	95	Queen

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

id	name	class	mark	h_name
4	Krish Star	Four	87	Jack
3	Arnold	Three	80	King
2	Max Ruin	Three	87	King
1	John Deo	Four	93	Queen
5	John Mike	Four	95	Queen

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

id	name	class	sex	student_id	mark
5	John Mike	Four	female	5	85.5000

LEFT JOIN ( Basic Query) SQL DUMP of these tables
RIGHT JOIN INNER JOIN : Table joining itself LINKING TABLES

Subscribe to our YouTube Channel here

MySQL left join using Multiple tables