困难的where子句，可能需要使用两个查询？

Question

I've got a query to produce which I can't seem to get right. I've got three tables:

student: student details

link: links that exist for a student, links have a status which can be active or completed

email: shows what links have been sent out by email.

I need to get a list of student IDs (from the student table) based on the following criteria:

1. link.status = active and related email doesn't exist (i.e. a link has been created but it hasn't been sent in an email)
2. link.status is null and email is null (i.e. there are no existing links for that student)
3. link.status = completed, and there are no other links for this student which have an active status

So if I have the following data in my tables:

student

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+

link

+----+-----------+------------+
| id |  status   | student_id |
+----+-----------+------------+
|  1 | completed |          1 |
|  2 | active    |          1 |
|  3 | completed |          2 |
|  4 | active    |          3 |
+----+-----------+------------+

email

+----+---------+
| id | link_id |
+----+---------+
|  1 |       1 |
|  2 |       2 |
|  3 |       3 |
+----+---------+

Then my query should return the following student IDs: 2,3,4

2 - because there is only a completed link for this student

3 - because there is an active link with no associated email

4 - because there are no links for this student

I currently have this query which gets part of what I need:

SELECT DISTINCT student.id
FROM student
LEFT JOIN link ON link.student_id = student.id
LEFT JOIN email ON email.link_id = link.id
WHERE student.course =  'phd'
AND student.institution_id =  '2'
AND (
  (link.status !=  "active" AND email.id IS NULL)
OR 
  (link.status IS NULL AND email.id IS NULL)
OR 
  (link.status =  "active" AND email.id IS NULL)
)

This of course doesn't get any student IDs where link.status = completed and no other links exist for the student. I can of course do this by adding in:

(link.status = "completed" and email.id IS NOT NULL)

into the WHERE, but this will return the student ID if they have another active link or they don't have an active link. This being the bit I'm struggling with.

I get the feeling this may not be able to be accomplished by a single query, so would I need to do two queries then subtract them from one another? I.e. the query above and a separate query selecting the links with a 'completed' status then subtracting them from the first query?

My application using these queries is built in PHP so I'm happy to do some logic in PHP with two queries if needed.

(Didn't have a clue what to put for the title so if anyone can think of anything better please edit it!)

Answer 1

SELECT s.* 
  FROM student s 
  LEFT 
  JOIN link l 
    ON l.student_id = s.id 
   AND l.status <> 'completed' 
  LEFT 
  JOIN email e 
    ON e.link_id = l.id 
 WHERE e.id IS NULL;

?