I am close but not quite there using a HAVING clause. I can't quite wrap my head around the logic.
There are 4 tables that come into play.
1) student table (accounts)
2) course table (courses)
3) course roster table (course_roster)
4) discussion posts table (discussion_posts)
the course roster table is a join table that has a record for each course a student is enrolled in (with course_id and account_id as foreign keys). The discussion posts table is a table that has a record for each post a student makes in any given course (again with course_id and account_id as foreign keys).
I am only given a list of potential courses (by course_id) to start with: ex 3002, 3007, 3010, 3099 etc.
based on that list of courses, I need to determine which students are registered for those courses, then determine if they have posted in ANY of those courses. I could do it with c# after I have retrieved the list by putting the students into an array if they haven't posted in one course, then removing them from the array on the next iteration if they have posted in another, but I'd like to do it all with the query if possible.
here is what I came up with that is not quite working yet.
Code: Select all
SELECT
a.last_name +', '+a.first_name AS name,
a.username,
c.title,
c.course_id,
a.user_id,
a.username,
COUNT(d.course_id) AS posts,
RTRIM(c.course_id) + RTRIM(a.username) AS identifier,
a.email
FROM
courses AS c
INNER JOIN
course_roster AS cr ON cr.course_id = c.course_id
INNER JOIN
accounts AS a ON a.username = cr.user_id
LEFT JOIN
discussion_posts AS d ON d.course_id = c.course_id AND
d.author = a.username
WHERE
c.course_id IN('30637', '30638', '30639', '30640')
GROUP BY
a.last_name+', '+a.first_name,
a.username,
c.title,
c.course_id,
a.user_id,
a.username,
a.email
HAVING
COUNT(d.course_id) = 0
AND
(
SELECT COUNT(DISTINCT(c.course_id))
FROM courses AS c
INNER JOIN
course_roster AS cr ON cr.course_id = c.course_id
INNER JOIN
accounts AS a ON a.username = cr.user_id
LEFT JOIN
discussion_posts AS d ON d.course_id = c.course_id AND
d.author = a.username
WHERE
cr.user_id = a.username
HAVING COUNT(d.course_id) = 0
)
=
(
SELECT
COUNT(DISTINCT(c.course_id))
FROM
courses AS c
INNER JOIN
course_roster AS cr ON cr.course_id = c.course_id
INNER JOIN
accounts AS a ON a.username = cr.user_id
WHERE
c.course_id IN('30637', '30638', '30639', '30640')
)