Menu
Community /Pin to ProfileBookmark
@NogDogOct 21.2020 — #Probably just need some parens:
That being said, I don't like seeing that same sub-query in two places, but you may need to do something like this, I _think_ (no promises ;) )...
SELECT WHERE JOINED Table Count is greater than..
I’m trying to run a query which will show all students who have a status of 1 (active) and they’re in class type 1 (general role) or if they’re in more than 5 classes.
This is what I have. It works except I get students in 0-5 classes as well. So I guess it doesn’t work.
“`
SELECT s.ID, s.StudentName, (SELECT COUNT(*) AS ttl FROM Classes i WHERE i.StudenID = s.ID) As Qnty
FROM `students` s
WHERE s.StudentStatus= 1 AND s.ClassType = 1 OR (SELECT COUNT(*) AS ttl FROM Classes i WHERE i.StudentID= s.ID) > 5
ORDER BY `Qnty` ASC
Sign in
to post a comment1 Comments(s) ↴
0
@NogDogOct 21.2020 — #Probably just need some parens:<i>
</i> WHERE s.StudentStatus= 1 AND (s.ClassType = 1 OR (SELECT COUNT(*) AS ttl FROM Classes i WHERE i.StudentID= s.ID) > 5)
That being said, I don't like seeing that same sub-query in two places, but you may need to do something like this, I _think_ (no promises ;) )...
<i>
</i>SELECT s.ID, s.StudentName, totals.total As Qnty
FROM students s
INNER JOIN ( -- Might need to be a LEFT JOIN???
SELECT StudentID, COUNT(*) AS total
FROM Classes c1
GROUP BY StudentID
) AS totals ON s.ID = totals.StudentID
WHERE s.StudentStatus= 1 AND (s.ClassType = 1 OR totals.total > 5)
ORDER BY Qnty ASC