title: SQL Server : how to join record not in another table?
body: <p>I want to show the appraisal submission status of the staff.</p>
  
  <p>It will add one record in the <code>Appraisal_Record</code> table if the file had been submitted.</p>
  
  <p>How can I write the SQL that shows all appraisal periods for all staff even though they had not submitted the appraisal (no records). Add a column "Status" to indicate the status, "Submitted" and "Not_Submitted" &lt;- if no record in [Appraisal_Record] table </p>
  
  <p>i want to use a case in sql statement to add a column... CASE WHEN xxx then "Submitted" ELSE "Not_Submitted" as Status </p>
  
  <p>Thanks</p>
  
  <p>The following is the example</p>
  
  <p>Table: <code>StaffDB</code></p>
  
  <pre><code>+---+-----+
  |SID|Name |
  +---+-----+
  |1  |Peter|
  +---+-----+
  |2  |John |
  +---+-----+
  |3  |Amy  |
  +---+-----+
  </code></pre>
  
  <p>Table: <code>Appraisal Period</code></p>
  
  <pre><code>+--------+-----------------+
  |PeriodID|PeriodDescription|
  +--------+-----------------+
  |1       |2012_Start_Term  |
  +--------+-----------------+
  |2       |2012_Mid_Term    |
  +--------+-----------------+
  |3       |2012_End_Term    |
  +--------+-----------------+
  </code></pre>
  
  <p>Table: <code>Appraisal_Record</code></p>
  
  <pre><code>+--+---+--------+
  |ID|SID|PeriodID|
  +--+---+--------+
  |1 |1  |1       |
  +--+---+--------+
  |2 |1  |2       |
  +--+---+--------+
  |3 |2  |3       |
  +--+---+--------+
  </code></pre>
  
  <p><strong>Result</strong>:</p>
  
  <pre><code>+---+-----+--------+-----------------+-------------+
  |SID|Name |PeriodID|PeriodDescription|Status       |
  +---+-----+--------+-----------------+-------------+
  |1  |Peter|1       |2012_Start_Term  |Submitted    |
  +---+-----+--------+-----------------+-------------+
  |1  |Peter|2       |2012_Mid_Term    |Submitted    |
  +---+-----+--------+-----------------+-------------+
  |1  |Peter|3       |2012_End_Term    |Not_Submitted|
  +---+-----+--------+-----------------+-------------+
  |2  |John |1       |2012_Start_Term  |Not_Submitted|
  +---+-----+--------+-----------------+-------------+
  |2  |John |2       |2012_Mid_Term    |Not_Submitted|
  +---+-----+--------+-----------------+-------------+
  |2  |John |3       |2012_End_Term    |Submitted    |
  +---+-----+--------+-----------------+-------------+
  |3  |Amy  |1       |2012_Start_Term  |Not_Submitted|
  +---+-----+--------+-----------------+-------------+
  |3  |Amy  |2       |2012_Mid_Term    |Not_Submitted|
  +---+-----+--------+-----------------+-------------+
  |3  |Amy  |3       |2012_End_Term    |Not_Submitted|
  +---+-----+--------+-----------------+-------------+
  </code></pre>
  
  <p>Thanks Kishore and Damien.</p>
  
  <p>The solution is:</p>
  
  <pre><code>select a.sid,a.name,b.periodid,b.perioddescription,
  CASE WHEN c.ID IS NOT NULL then 'Submitted' ELSE 'Not_Submitted' as Status
  from 
  StaffDB a 
  cross join 
  [Appraisal Period] b
  left join 
  Appraisal_Record c on a.sid = c.sid and b.periodid =  c.periodid
  </code></pre>