These are the tables:
professor
+-------+--------+--------+--------+------+
| empid | name | status | salary | age |
+-------+--------+--------+--------+------+
| 1 | Arun | 1 | 2000 | 23 |
| 2 | Benoy | 0 | 3000 | 25 |
| 3 | Chacko | 1 | 1000 | 36 |
| 4 | Divin | 0 | 5000 | 32 |
| 5 | Edwin | 1 | 2500 | 55 |
| 7 | George | 0 | 1500 | 46 |
+-------+--------+--------+--------+------+
works
+----------+-------+---------+
| courseid | empid | classid |
+----------+-------+---------+
| 1 | 1 | 10 |
| 2 | 2 | 9 |
| 3 | 3 | 8 |
| 4 | 4 | 10 |
| 5 | 5 | 9 |
| 6 | 1 | 9 |
+----------+-------+---------+
The above are the tables from which I need to retrieve the data from.
The question is to return list of Employees who take both Class 10 and Class 9.
The query I have written is:
select professor.name
from inner join works
on professor.empid=works.empid
where works.classid=9 and works.classid=10;
I know that the result I want is Arun, but I don't know what should be the exact query to retrieve the required result.
He wants the professeors that take class 9 AND 10. So there are 2 different records in works that need to match.
select professor.name from professor
join works A on A.empid=professor.empid and A.classid=9
join works B on B.empid=professor.empid and B.classid=10