[RESOLVED] How to assign which table to LIMIT in from a JOIN?

[curb]

I'm joining two tables "classrooms" and "students".

'Classrooms' has 7 school subjects and 'Students' has a list of students with their grades from the classrooms.

I'm trying to do a while loop pulling a list of all the classrooms and pull only 1 student with the highest grade displayed next to the classroom.

I did a JOIN but it's displaying all the students on each class.

Example:

English - student1
English - student2
English - student3
English - student4
English - student5
Science - student1
Science - student2
Science - student3
Science - student4
Science - student5

When I want this displayed:

English - student3 (highest grade)
Science - student1 (highest grade)

[laserlight]

What is the relevant database schema?

[curb]

mysql_query("
CREATE TABLE `school_students` (
`s_id` INT NOT NULL AUTO_INCREMENT ,
`s_firstname` TINYTEXT NOT NULL ,
`s_lastname` TINYTEXT NOT NULL ,
`s_subject` TINYTEXT NOT NULL ,
`s_grade` TINYTEXT NOT NULL ,
PRIMARY KEY ( `i_id` )
) ENGINE = MYISAM ;
");

mysql_query("
CREATE TABLE `school_class` (
`c_id` INT NOT NULL AUTO_INCREMENT ,
`c_subject` TINYTEXT NOT NULL ,
PRIMARY KEY ( `c_id` )
) ENGINE = MYISAM ;
");

[curb]

Thanks laserlight for looking into this 🙂. I have found an alternative solution.