MySQL customized join query using multiple tables
- by itgeek
I am searching one student from each class from one group. There are different class groups and every group has different classes and every class has multiple students. See below:
Group1 --> Class1, Class2 etc
Class1 --> GreenStudent1, GreenStudent2 etc
Class2 --> RedStudent1, RedStudent2 etc
------------------------------------------------------
SELECT
table1.id,
table1.myname,
table1.marks
table2.studentid,
table2.studentname
FROM table1
INNER JOIN table3 ON table1.oldid = table3.id
INNER JOIN table2 ON table2.studentid = table3.newid
WHERE
table1.classgroup = 'SCI79'
GROUP BY table1.oldid
ORDER BY table1.marks DESC
There are different joins applied in the query. Above mentioned query giving me correct results but I need little modification in it. Current query returning me one student from each class.
What I need? I need one student from each class but only that student who has MAXIMUM
table1.marks
So I should have one student from each class who has maximum number in their relevant classes. Can anyone suggest some solution or rewrite this query? Thanks :)
