mysql query using inner joins and subquery

Question

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.

Please help me out !!

Michael Berkowski · Accepted Answer · 2014-01-30 17:52:24Z

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.

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.

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

mellowfish · Accepted Answer · 2014-01-30 17:54:40Z

0

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

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');

answered Jan 30, 2014 at 17:54

mellowfish

6846 silver badges14 bronze badges

Collectives™ on Stack Overflow

mysql query using inner joins and subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related