What is the correct way to fix an ambiguous SQL syntax error?

Question

I am writing a query that will find the names of students enrolled in the maximum number of classes from the following relation. I am using MySQL server and working with MySQL Workbench.

 Student(snum: integer, sname: string, major: string, level: string, age: integer) 
 Class(name: string, meets_at: time, room: string, fid: integer) 
 Enrolled(snum: integer, cname: string) Faculty(fid: integer, fnarne: string, deptid: integer)

Here is how I tried to implement the query.

SELECT F.fname , COUNT(*) AS CourseCount
FROM faculty F, class C
WHERE F.fid  = C.fid 
GROUP BY F.fid , F.fname 
HAVING EVERY (C.room = 'R128');

However I keep getting this error which I can not fix.

 Error Code: 1064. You have an error in your SQL syntax; check the manual 
 that corresponds to your MySQL server version for the right syntax to use 
 near '(C.room = 'R128')' 

Answer 1

You can try below - every is not a valid syntax that's why you got error

select * from 
(
select F.fname, count(*) as CourseCount
from faculty as F
join class as C on C.fid = F.fid and C.room = 'R128'
group by F.fid, F.fname
)A where CourseCount in (select max(coursecount) from (select F.fname, count(*) as CourseCount
from faculty as F
join class as C on C.fid = F.fid and C.room = 'R128'
group by F.fid, F.fname)B)