sql server - I can't apply multiple conditions in this SQL statement

Question

I don't know why it doesn't give me the answer for students enrolled in Database Systems but not in Operating System Design.

select student.snum, student.sname, enrolled.cname
    -> from enrolled
    -> inner join student ON enrolled.snum = student.snum
    -> where enrolled.cname="Database Systems" AND enrolled.cname<>"Operating System Design";`
+-----------+--------------------+------------------+
| snum      | sname              | cname            |
+-----------+--------------------+------------------+
| 112348546 | Joseph Thompson    | Database Systems |
| 115987938 | Christopher Garcia | Database Systems |
| 348121549 | Paul Hall          | Database Systems |
| 322654189 | Lisa Walker        | Database Systems |
| 552455318 | Ana Lopez          | Database Systems |
+-----------+--------------------+------------------+

My student table.

+-----------+--------------------+------------------------+-------+------+
| snum      | sname              | major                  | level | age  |
+-----------+--------------------+------------------------+-------+------+
|  51135593 | Maria White        | English                | SR    |   21 |
|  60839453 | Charles Harris     | Architecture           | SR    |   22 |
|  99354543 | Susan Martin       | Law                    | JR    |   20 |
| 112348546 | Joseph Thompson    | Computer Science       | SO    |   19 |
| 115987938 | Christopher Garcia | Computer Science       | JR    |   20 |
| 132977562 | Angela Martinez    | History                | SR    |   20 |
| 269734834 | Thomas Robinson    | Psychology             | SO    |   18 |
| 280158572 | Margaret Clark     | Animal Science         | FR    |   18 |
| 301221823 | Juan Rodriguez     | Psychology             | JR    |   20 |
| 318548912 | Dorthy Lewis       | Finance                | FR    |   18 |
| 320874981 | Daniel Lee         | Electrical Engineering | FR    |   17 |
| 322654189 | Lisa Walker        | Computer Science       | SO    |   17 |
| 348121549 | Paul Hall          | Computer Science       | JR    |   18 |
| 351565322 | Nancy Allen        | Accounting             | JR    |   19 |
| 451519864 | Mark Young         | Finance                | FR    |   18 |
| 455798411 | Luis Hernandez     | Electrical Engineering | FR    |   17 |
| 462156489 | Donald King        | Mechanical Engineering | SO    |   19 |
| 550156548 | George Wright      | Education              | SR    |   21 |
| 552455318 | Ana Lopez          | Computer Engineering   | SR    |   19 |
| 556784565 | Kenneth Hill       | Civil Engineering      | SR    |   21 |
| 567354612 | Karen Scott        | Computer Engineering   | FR    |   18 |
| 573284895 | Steven Green       | Kinesiology            | SO    |   19 |
| 574489456 | Betty Adams        | Economics              | JR    |   20 |
| 578875478 | Edward Baker       | Veterinary Medicine    | SR    |   21 |
+-----------+--------------------+------------------------+-------+------+

My enrolled table

+-----------+----------------------------+
| snum      | cname                      |
+-----------+----------------------------+
| 112348546 | Database Systems           |
| 115987938 | Database Systems           |
| 348121549 | Database Systems           |
| 322654189 | Database Systems           |
| 552455318 | Database Systems           |
| 455798411 | Operating System Design    |
| 552455318 | Operating System Design    |
| 567354612 | Operating System Design    |
| 112348546 | Operating System Design    |
| 115987938 | Operating System Design    |
| 322654189 | Operating System Design    |
| 567354612 | Data Structures            |
| 552455318 | Communication Networks     |
| 455798411 | Optical Electronics        |
| 301221823 | Perception                 |
| 301221823 | Social Cognition           |
| 301221823 | American Political Parties |
| 556784565 | Air Quality Engineering    |
|  99354543 | Patent Law                 |
| 574489456 | Urban Economics            |
+-----------+----------------------------+

question from:https://stackoverflow.com/questions/65914142/i-cant-apply-multiple-conditions-in-this-sql-statement

Answer 1

You need to use the NOT EXISTS as follows:

select s.snum, s.sname, e.cname
  from enrolled e 
  inner join student s ON e.snum = s.snum
  where e.cname='Database Systems'
    AND not exists 
        (select 1 from enrolled ee
          where ee.snum = e.snum and e.cname = 'Operating System Design');