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:
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!)
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;
?
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments