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!

Answers


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

Need Your Help

Elastic Beanstalk without Elastic Load Balancer

amazon-web-services amazon-ec2 cloud load-balancing elastic-beanstalk

I would like to switch of Elastic Load Balancer (ELB) for my Elastic Beanstalk environment.

cocoa-applescript: Determinate progress indicator

xcode cocoa progress-bar applescript-objc

I am making an application using Cocoa-Applescript which identifies local IP addresses which are up and running, by pinging x.y.z.[1-255] and appends running IPs to a text file. I already have a GU...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.