CASE in T-SQL in SQL Server 2005/2008

Can you please explain the logic behind this kind of scenario

I have 2 tables

STUDENT TABLE

CHECK-UP(PK)      STUDENTID            DATE
1001                 1               06/15/1980
1002                 1               07/30/1980
1003                 1               12/28/1980
1004                 2               03/22/1981
1005                 2               01/28/1981
1006                 3               04/20/1981

ACTIVITY TABLE

   CHECK-UP            INDEX              VALUE
    1001                 1                SMOKING
    1001                 2                ALCOHOL
    1001                 3                DRUGS
    1002                 1                SMOKING
    1002                 2                ALCOHOL
    1003                 1                SMOKING
    1003                 3                DRUGS
    1004                 3                DRUGS

I was using a simple inner join between the two tables to get all the records which matches the value '%SMOKING%'. It gives me 3 rows which has smoking values and the result set will be 1001,1002,1003 and corresponding studentsid will be 1.

What will I have to do to find the smoking information for all the students?

In that case I have to do a left join between the tables first to get all the records but that is not working in my case to include all the students. Please advice. Below is the piece of code.

Select DISTINCT STUDENTID
               ,CHECK-UP
               ,INDEX
               ,CAST(VALUE as VARCHAR(MAX)) --CASTING IT SINCE IT IS A TEXT FIELD
               ,CASE
                      WHEN (PATINDEX('%SMOKING%',act.VALUE)) THEN
                             'LMDO'
                      WHEN (PATINDEX('%NOT SMOKING%',act.VALUE)) THEN
                             'LMD1'
                      WHEN (ISNULL(CAST(act.VALUE as varchar(max)),'')='') THEN
              开发者_运维问答                  'CLEAR'
                      ELSE 
                                 'CLEAR'
                  END done
FROM STUDENT stu
LEFT JOIN ACTIVITY act
ON stu.CHECK-UP=act.CHECK-UP
WHERE ??

If suppose for studentID=3 the student has no row for smoking. I want that value to go into the 'CLEAR' bucket. ALSO I cannot put a condition in the WHERE CLAUSE as it will restrict my data. What can be done to resolve this issue?

I expect a result set of something like this:

STUDENTID        CHECK-UP         INDEX          VALUE        DONE     DATE
   1               1001             1           SMOKING       LMDO     06/15/1980
   1               1002             1           SMOKING       LMDO     07/30/1980
   1               1003             1           SMOKING       LMDO     12/28/1980
   2               1004            NULL          NULL         CLEAR    03/22/1981
   2               1005            NULL          NULL         CLEAR    01/28/1981
   3               1006            NULL          NULL         CLEAR    04/20/1981

Based on your example results and original query I assume you need something along these lines.

SELECT  S.STUDENTID ,
        A.[CHECK-UP] ,
        A.[INDEX] ,
        A.[VALUE] ,
        CASE [VALUE]
          WHEN 'SMOKING' THEN 'LMDO'
          WHEN 'NOT SMOKING' THEN 'LMD1'
          ELSE 'CLEAR'
        END done ,
        S.[DATE]
FROM    STUDENT S
        LEFT JOIN ACTIVITY A ON S.[CHECK-UP] = A.[CHECK-UP]
                                AND A.[INDEX] = 1