title: CASE in T-SQL in SQL Server 2005/2008
body: <p>Can you please explain the logic behind this kind of scenario</p>
  
  <p>I have 2 tables</p>
  
  <p><strong>STUDENT TABLE</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>ACTIVITY TABLE</strong></p>
  
  <pre><code>   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
  </code></pre>
  
  <p>I was using a simple inner join between the two tables to get all the records which matches the value <code>'%SMOKING%'</code>. It gives me 3 rows which has smoking values and the result set will be <code>1001,1002,1003</code> and corresponding studentsid will be 1. </p>
  
  <p>What will I have to do to find the smoking information for all the students?</p>
  
  <p>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.</p>
  
  <pre><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 ??
  </code></pre>
  
  <p>If suppose for <code>studentID=3</code> 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?</p>
  
  <p>I expect a result set of something like this:</p>
  
  <pre><code>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
  </code></pre>