SELECT record NOT IN SET of Record

Question

student table

|----------------------|
| student_id | name    |
|------------|---------|
| 1          | Richard |
| 2          | Emily   |
| 3          | Hans    |
|------------|---------|

lecturer table

|--------------------|
| lecturer_id | name |
|-------------|------|
| 1           | John |
| 2           | Mike |
|-------------|------|

classes table

|-----------------------------------------------|
| class_id | lecturer_id | material             |
|----------|-------------|----------------------|
| 1        | 1           | Basic of algorithm   |
| 2        | 1           | Basic of programming |
| 3        | 2           | Database  Essentials |
| 4        | 2           | Basic of SQL         |
|----------|-------------|----------------------|

attendance table

|-----------------------|
| class_id | student_id |
|----------|------------|
| 1        | 1          |
| 1        | 2          |
| 1        | 3          |
| 2        | 1          |
| 2        | 2          |
| 3        | 1          |
| 3        | 2          |
| 3        | 3          |
| 4        | 1          |
| 4        | 2          |
|----------|------------|

how to show classes records (from classes table) that not attended by Hans (student) in MySQL?

desired result :

|-----------------------------------------------|
| class_id | lecturer_id | material             |
|----------|-------------|----------------------|
| 2        | 1           | Basic of programming |
| 4        | 2           | Basic of SQL         |
|----------|-------------|----------------------|

Dup of [this question](https://stackoverflow.com/questions/1519272/mysql-not-in-query) — danblack, Feb 11 '19 at 04:36
Possible duplicate of [MySQL "NOT IN" query](https://stackoverflow.com/questions/1519272/mysql-not-in-query) — user1506104, Feb 11 '19 at 04:37
i need specific result from specific parameter (Hans only), `WHERE student_id = 3` — codefighter, Feb 11 '19 at 04:39

score 1 · Answer 1 · answered Feb 11 '19 at 04:37

One approach uses EXISTS:

SELECT c.class_id, c.lecturer_id, c.material
FROM classes c
WHERE NOT EXISTS (SELECT 1 FROM attendance a
                  INNER JOIN student s
                      ON a.student_id = s.student_id
                  WHERE a.class_id = c.class_id AND
                        s.name = 'Hans');

score 1 · Answer 2 · answered Feb 11 '19 at 04:53

1

Using joins -

select c.class_id
from attendance a inner join student s on (a.student_id=s.student_id and s.student_id='Hans')
right outer join classes c on (a.class_id=c.class_id)
where a.class_id is null

answered Feb 11 '19 at 04:53

Vijiy

1,187
6
21

Note that nobody really uses RIGHT JOIN – Strawberry Feb 11 '19 at 08:09
@Strawberry I have never met anyone named "Nobody." Are you really sure that anyone uses right joins? – Tim Biegeleisen Feb 11 '19 at 08:47

SELECT record NOT IN SET of Record

how to show classes records (from classes table) that not attended by Hans (student) in MySQL?

2 Answers2