How to JOIN a table only if results have specific parameter

Question

I need to get the user_name from all users that have completed 2 or more classes giving the following table structure...

table users
===============================
user_id     |   user_name
===============================
1234            completed_two
1235            completed_cero
1236            completed_one
1237            completed_two_no_survey

table class_registration
========================================================================
reg_id  | class_id    |   class_type      |   user_id     |   attendance
========================================================================
1            1            stress                1234            1
2            1            stress                1236            1
3            2            who_cares             1234            1
4            1            stress                1235            0
5            5            nutrition             1236            1
6            9            who_cares_2           1237            1
7            10           return_of_the_care    1237            1

table surveys
==================================================================
survey_id   |  user_id   |   class_id    |      survey_type
==================================================================
1               1234            1           'Pre Workshop Survey'
2               1236            1           'Pre Workshop Survey'
3               1235            1           'Pre Workshop Survey'
4               1236            2           'Pre Workshop Survey'
5               1234            1           'Post Workshop Survey'
6               1236            2           'Post Workshop Survey'

In this sample, user_id 1234 has a class of type who_cares that attendance is set to 1 so this counts towards 1 of the classes. The class of type stress has additional requirements, mainly there must be a post survey completed in the surveys table. The user with user_id has also attended 2 classes, but for the stress class the survey is missing. The user with user_id of 1235 did registered for the stress class but failed to attend.

The results should contain the user names completed_two and completed_two_no_survey since this 2 users have a total of 2 classes attended even tho, user 1237 has no surveys. Only class_type stress or class_type nutrition has surveys.

I don't even know where to begin to do such a query... I'm sure there is a CASE I can trow in there, but I'm not sure how to make it so that only stress and nutrition are checked against the surveys table. I figured is something like this, with a CASE clause in there somewhere...

SELECT users.user_name FROM class_registration
JOIN users ON users.user_id = class_registration.user_id
WHERE class_registration.attendance = 1
GROUP BY class_registration.user_id, users.user_name
HAVING COUNT(class_registration.attendance) >= 2

Again, what I need is to check the surveys table for a post workshop survey if and only if the class_type is stress or nutrition.

Answer 1

declare @users table (user_id int, user_name varchar(50))
insert into @users values 
(1234,            'completed_two'),
(1235,            'completed_cero'),
(1236,            'completed_one'),
(1237,            'completed_two_no_survey')

declare @class_registration table
(reg_id int, class_id int, class_type varchar(50), user_id int, attendance bit)
insert into @class_registration values
(1,            1,            'stress',                1234,            1),
(2,            1,            'stress',                1236,            1),
(3,            2,            'who_cares',             1234,            1),
(4,            1,            'stress',                1235,            0),
(5,            5,            'nutrition',             1236,            1),
(6,            9,            'who_cares_2',           1237,            1),
(7,            10,           'return_of_the_care',    1237,            1)

declare @surveys table
(survey_id int, user_id int, class_id int, survey_type varchar(50))
insert into @surveys values
(1,               1234,            1,           'Pre Workshop Survey'),
(2,               1236,            1,           'Pre Workshop Survey'),
(3,               1235,            1,           'Pre Workshop Survey'),
(4,               1236,            2,           'Pre Workshop Survey'),
(5,               1234,            1,           'Post Workshop Survey'),
(6,               1236,            2,           'Post Workshop Survey')


select u.user_id, u.user_name
from @users u
  inner join
    (  
      select c.user_id
      from @class_registration as c
        inner join @surveys as s
          on c.class_id = s.class_id and
             c.user_id = s.user_id
      where
        class_type in ('nutrition', 'stress') and
        survey_type = 'Post Workshop Survey' and
        attendance = 1
      union all  
      select c.user_id
      from @class_registration as c
      where
        class_type not in ('nutrition', 'stress') and
        attendance = 1
    ) as cc
    on u.user_id = cc.user_id
group by u.user_id, u.user_name
having count(*) > 1

Answer 2

Hope I interpreted your question correctly - some of your examples are missing the actual user IDs...

select  u.user_name, 
        cr.class_type,
        count(*)
from    users                  u, 
        class_registration     cr
where   u.user_id    =    cr.user_id
and     cr.class_type in ('stress', 'nutrition')
and     cr.attendance = 1
and     user_id not in
(select user_id
from    surveys
where   user_id = u.user_id
and     class_id = cr.class_id)
having count(*) > 1