使用内部联接和子查询的mysql查询

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 |
|        2 |     3 |      10 |
|        2 |     1 |       7 |
+----------+-------+---------+

course:
+----------+------------+--------+
| courseid | coursename | points |
+----------+------------+--------+
|        1 | Maths      |    100 |
|        2 | Science    |     80 |
|        3 | English    |     85 |
|        4 | Social     |     90 |
|        5 | Malayalam  |     99 |
|        6 | Arts       |     40 |
+----------+------------+--------+

The question is :

问题是 :

Return list of employees who have taught course Maths or Science but not both

已经教过数学或科学课程而不是两者兼有的员工的返回清单

The query which I wrote is :

我写的查询是:

select distinct professor.name from professor
inner join works
on professor.empid=works.empid
where works.courseid in
(select courseid from course where coursename ='Maths' or coursename='Science');

The output I received is:

我收到的输出是:

Arun
Benoy
Chacko

Here the employee 'Arun' shouldnt have been displayed as he as taught both Maths and Science.

在这里,员工'Arun'不应该像他教授数学和科学一样展示。

Please help me out !!

请帮帮我!!

2 个解决方案

#1

You may use an aggregate COUNT() to check that the total number of DISTINCT courses taught is exactly 1, while still filtering to the two different types of courses. That ensures that only one, never both, is returned.

您可以使用聚合COUNT()来检查DISTINCT课程的总数是否正好为1,同时仍然可以过滤到两种不同类型的课程。这确保只返回一个,而不是两个。

Because the IN () limits all rows initially returned only to the two desired courses, professors can have a maximum of 2 possible different courses via COUNT(DISTINCT coursename). A HAVING clause then prohibits those with 2 from the final result set.

因为IN()限制了最初只返回两个所需课程的所有行,教授可以通过COUNT(DISTINCT coursename)最多可以有2个不同的课程。然后,HAVING子句禁止那些来自最终结果集的2。

SELECT
  DISTINCT professor.name
FROM
  professor
  INNER JOIN works ON professor.empid = works.empid
  /* Join against course to get the course names */
  INNER JOIN course ON works.courseid = course.courseid
WHERE
  /* Restrict only to Maths, Science */
  course.coursename IN ('Maths', 'Science')
GROUP BY professor.name
/* Only those with exactly one type of course */
HAVING COUNT(DISTINCT course.coursename) = 1

Here is a demonstration: http://sqlfiddle.com/#!2/2e9610/2

这是一个演示:http://sqlfiddle.com/#!2/2e9610/2

#2

You want to use an xor here instead of an or.

你想在这里使用xor而不是或。

select distinct professor.name from professor
inner join works
on professor.empid=works.empid
where works.courseid in
(select courseid from course where coursename ='Maths' xor coursename='Science');

#1