MYSQL Query not working, is there a simpler query for this case?
I have a STUDENT table which contains data about students from a faculty:
STUDENT(ID, Name, Grade, ID_Tutor)
Structure and data:
CREATE TABLE STUDENT (`ID` int, `Name` varchar(5), `Grade` int, `ID_Tutor` int); INSERT INTO STUDENT (`ID`, `Name`, `Grade`, `ID_Tutor`) VALUES (1, 'A', 10, NULL), (2, 'B', 9.5, 1), (3, 'C', 9, 1), (4, 'D', 8, 1), (5, 'E', 7, 4), (6, 'F', 8, 1), (7, 'G', 5, 7), (10, 'H', 6, 5) (11, 'I', 7, 3), (12, 'J', 9, 10), (13, 'K', 9.6, 11), (14, 'L', 8.9, 5), (15, 'M', 9.9, 13), (16, 'N', 7, 2), (17, 'O', 7.9, 2), (18, 'P', 2, 15);
Top 5 faculty grades:
`ID`, `Name`, `Grade`, `ID_Tutor` (1, 'A', 10, NULL), (15, 'M', 9.9, 13), (13, 'K', 9.6, 11), (2, 'B', 9.5, 1), (3, 'C', 9, 1), (12, 'J', 9, 10),
In result should appear students with these tutors from the above table.
I want to make a SQL query (MYSQL) to find
- student name,
- tutor name and
- tutor grade
for students whose tutor has a grade in top 5 faculty students (and without using LIMIT and/or ROWNUM - for a Oracle solution).
Result should look like this:
| student name | tutor name | tutor grade | 'B' 'A' 10 'C' 'A' 10 'D' 'A' 10 'F' 'A' 10 'P' 'M' 9.9 'M' 'K' 9.6 'N' 'B' 9.5 'O' 'B' 9.5 'I' 'C' 9
I tried something like this (but not working) and I think I'm complicating a lot... (and I should not use ROWNUM)
SELECT s.ID, s.Name Student, s.Grade, p.ID_Tutor, p.Grade FROM STUDENT s, (SELECT * FROM (SELECT * FROM (SELECT t.Name Tutor , t.ID , Grade Grade_Tutor FROM STUDENT s JOIN STUDENT t on s.ID_Tutor = t.ID GROUP BY t.Name, t.ID) ORDER BY 3 desc ) WHERE ROWNUM < 6) p WHERE s.ID_Tutor = p.ID
Give me this error:
#1248 - Every derived table must have its own alias
Thanks in advance!
Your are lacking of several table alias on your code. Check this working SQL Fiddle code. But it does not return any value as I just added some dumb values. Feel free to use it and test your results.
SELECT s.ID, s.Name Student, s.Grade, p.ID_Tutor, p.Grade_tutor FROM STUDENT s, (SELECT * FROM (SELECT * FROM (SELECT t.Name Tutor, t.ID , s.Grade Grade_Tutor, s.ID_Tutor FROM STUDENT s JOIN STUDENT t on s.ID_Tutor = t.ID GROUP BY t.Name, t.ID) innerTable ORDER BY 3 desc ) secondInnerTable WHERE @ROWNUM < 6) p WHERE s.ID_Tutor = p.ID
Many thanks to @TehShrike, is his answer!!!
The only "downside" is that is using LIMIT, but if someone has a version without LIMIT please post it; until then, thanks again, @TehShrike!
SELECT student.NAME AS student_name, top_five_tutors.NAME AS tutor_name, top_five_tutors.grade AS tutor_grade FROM ( SELECT tutor.id, tutor.NAME, tutor.Grade FROM ( SELECT DISTINCT id_tutor AS id FROM student ) AS tutor_ids JOIN student AS tutor ON tutor.id = tutor_ids.id ORDER BY tutor.grade DESC LIMIT 5 ) AS top_five_tutors JOIN student ON student.id_tutor = top_five_tutors.id