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
0 comments:
Post a Comment