Page 1 of 1

selecting users who haven't posted in ANY discussion forums

Posted: Wed May 09, 2007 11:41 am
by Burrito
I need to select students (MSSQL) who have not posted in ANY discussion forums for ANY course in which they are enrolled.

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') 
)

Posted: Wed May 09, 2007 1:10 pm
by Begby
This should be a lot simpler than what you have. Here is a solution (untested but probably correct) that is for MySQL.

Code: Select all

SELECT
a.*
FROM
accounts AS a
WHERE NOT EXISTS(
 SELECT *
 FROM
 course_roster AS cr
 INNER JOIN
 discussion_posts AS d ON d.course_id = c.course_id
 WHERE cr.user_id = a.username
 AND cr.course_id IN('30637', '30638', '30639', '30640') 
)
Another solution would simply be getting a count of the total number of posts and using having postCount = 0.

Code: Select all

SELECT
a.username,
count(d.course_id) as postCount
FROM
accounts AS a
INNER JOIN
course_roster AS cr ON cr.userID = a.username
INNER JOIN
discussion_posts AS d ON d.course_id = c.course_id
WHERE cr.course_id IN('30637', '30638', '30639', '30640') 
HAVING postCount = 0
GROUP BY a.username

Posted: Wed May 09, 2007 4:24 pm
by Burrito
I got it figured. I had to change the alias in my sub-queries for the users table to point to the original declaration inside my where clause.

Posted: Thu May 10, 2007 12:50 am
by timvw
I find it a bit out that you do an INNER JOIN (since that would exclude all users that don't have an entry in the other table)...

If you did an OUTER JOIN you would get all the users, and if they don't have a row in the other table, the values would be filled with nulls...

Posted: Thu May 10, 2007 11:04 am
by Burrito
I do inner joins on the schedule table but I do a left outer for the discussion posts table. that should be the only place that I need to left join as if the students don't exists on the schedule table, I don't need to return anything, but if they don't have a record on the discussion posts table, I need to return a null as you suggest.