Difficult where clause, may require the use of two queries?

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

Because your request is based on links and one student can have more than one link, you should start with query to links table, after that, you add joins and conditions.

Preparation SQL:

CREATE TABLE IF NOT EXISTS student 
  (
    id int auto_increment primary key, 
    course tinytext, 
    institution_id int
  );
INSERT INTO student (id, course, institution_id) VALUES
  (1, 'phd', 2),
  (2, 'phd', 2),
  (3, 'phd', 2),
  (4, 'phd', 2);
CREATE TABLE IF NOT EXISTS link 
  (
    id int auto_increment primary key, 
    status tinytext, 
    student_id int
  );
INSERT INTO link (id, status, student_id) VALUES
  (1, 'completed', 1),
  (2, 'active', 1),
  (3, 'completed', 2),
  (4, 'active', 3);
CREATE TABLE IF NOT EXISTS email 
  (
    id int auto_increment primary key, 
    link_id int
  );
INSERT INTO email (id, link_id) VALUES
  (1, 1),
  (2, 2),
  (3, 3);

Query:

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

Play with it: http://sqlfiddle.com/#!2/dae16b/2

I don't really understand your question, because you have many mistakes in it. I will try dig deeper to find if your logic is ok.

EDIT: "Strawberry" approach by filtering JOIN's could be the thing you need

SELECT s.id 
  FROM student s 
  LEFT JOIN link l 
    ON l.student_id = s.id AND l.status = 'active' OR l.status IS NULL 
  LEFT JOIN email e 
    ON e.link_id = l.id 
WHERE 
  e.id IS NULL 
  AND s.course =  'phd' 
  AND s.institution_id =  '2';

Play with it: http://sqlfiddle.com/#!2/dae16b/26

We select "student" table and add only those links, that have "active" or "null" status (LEFT JOIN link l ON l.student_id = s.id AND l.status != 'completed'), which solves for rule #2 (there are no existing links for that student) and first part of rule #1 (a link has been created but it hasn't been sent in an email) and second part of rule #3 (link.status = completed, and there are no other links for this student which have an active status). After that, to solve for second par of rule #2 (a link has been created but it hasn't been sent in an email), we remove rows which does not have an e-mail (JOIN email e ON e.link_id = l.id and e.id IS NULL part).

Only thing left is to think, if you need to solve for first part of rule #3 (link.status = completed, and there are no other links for this student which have an active status), because I do not know, if situation where "student has no links" = "student has link.completed status".

For now, this query returns what you requested.

Answer 2

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;

?