You have missed the case in which same faculty teaches more than one topic in the same room at different times.
Example:
name |
fid |
room |
DS-Theory |
7 |
1105 |
Algo-Theory |
7 |
1105 |
According to your query, faculty name with fid=7 will also come into the result but it should not.
So taking the above case into consideration, I think below SQL query should be the correct one:
SQL Query
select fname from faculty NATURAL JOIN classroom group by faculty.fid having count(distinct room)=(select count(distinct room) from classroom);
|
Please correct, If I am mistaken.