title: SQL Left Join (Multiple Join Condition)
body: <p>I have two derived tables named Check Ins and Check Outs</p>
  
  <p><strong>Check Ins</strong></p>
  
  <pre><code>CheckDate  CheckIn
  ---------- ---------
  08/02/2011 10:10:03
  08/02/2011 15:57:16
  07/19/2011 13:58:52
  07/19/2011 16:50:55
  07/26/2011 15:11:24
  06/21/2011 12:36:47
  08/16/2011 14:49:36
  08/09/2011 13:52:10
  08/09/2011 16:54:51
  08/23/2011 15:48:58
  09/06/2011 15:23:00
  09/13/2011 10:09:27
  09/13/2011 10:40:14
  09/13/2011 11:43:14
  09/13/2011 11:59:32
  09/13/2011 17:05:24
  09/20/2011 11:03:42
  09/20/2011 12:08:50
  09/20/2011 15:21:06
  09/20/2011 15:34:29
  09/27/2011 11:34:06
  10/04/2011 11:37:59
  10/04/2011 15:24:04
  10/04/2011 16:57:44
  10/11/2011 18:19:33
  </code></pre>
  
  <p><strong>Check Outs</strong></p>
  
  <pre><code>CheckDate  CheckOut
  ---------- ---------
  08/02/2011 13:29:40
  08/02/2011 17:02:25
  07/12/2011 17:06:06
  07/19/2011 16:40:15
  07/19/2011 17:07:35
  07/26/2011 14:48:10
  07/26/2011 17:27:08
  05/31/2011 17:01:39
  06/07/2011 17:04:29
  06/14/2011 17:08:50
  06/21/2011 17:03:46
  06/28/2011 17:10:45
  07/05/2011 17:02:48
  08/16/2011 13:37:36
  08/16/2011 17:06:34
  08/09/2011 12:00:29
  08/09/2011 13:29:36
  08/09/2011 14:36:09
  08/09/2011 17:00:38
  08/23/2011 13:37:11
  08/23/2011 17:01:37
  09/06/2011 17:00:09
  09/13/2011 10:11:50
  09/13/2011 11:22:02
  09/13/2011 11:47:35
  09/13/2011 14:13:36
  09/13/2011 14:14:25
  09/13/2011 17:08:43
  09/20/2011 09:54:55
  09/20/2011 11:55:31
  09/20/2011 11:55:36
  09/20/2011 13:35:16
  09/20/2011 15:26:02
  09/20/2011 16:33:21
  09/20/2011 17:07:52
  09/27/2011 11:12:38
  10/04/2011 13:26:31
  10/04/2011 16:32:56
  10/04/2011 17:02:35
  10/11/2011 18:25:32
  </code></pre>
  
  <p>I want to pair the check ins with the check outs given the condition that both logs are on the same date and check out values cannot be earlier than check ins, so I have come up with this query</p>
  
  <pre><code>  SELECT A.ChkDt
        AS CheckDate,
           B.CheckIn,
           MIN(A.ChkTm)
        AS CheckOut
      FROM #tempAttLogs
        AS A LEFT JOIN
           (SELECT ChkDt
                AS CheckDate,
                   MIN(ChkTm)
                AS CheckIn
              FROM #tempAttLogs
             WHERE ChkTp = 'I'
          GROUP BY ChkDt) B
        ON A.ChkDt = B.CheckDate
     WHERE ChkTp = 'O' AND
           A.ChkTm &gt; B.CheckIn
  GROUP BY A.ChkDt, B.CheckIn
  </code></pre>
  
  <p>With a result set of</p>
  
  <pre><code>CheckDate  CheckIn   CheckOut
  ---------- --------- ---------
  06/21/2011 12:36:47  17:03:46
  07/19/2011 13:58:52  16:40:15
  07/26/2011 15:11:24  17:27:08
  08/02/2011 10:10:03  13:29:40
  08/09/2011 13:52:10  14:36:09
  08/16/2011 14:49:36  17:06:34
  08/23/2011 15:48:58  17:01:37
  09/06/2011 15:23:00  17:00:09
  09/13/2011 10:09:27  10:11:50
  09/20/2011 11:03:42  11:55:31
  10/04/2011 11:37:59  13:26:31
  10/11/2011 18:19:33  18:25:32
  </code></pre>
  
  <p>The thing is I also want to include the logs with no corresponding pairs. How can this be possible?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>My expected result set must include logs like this</p>
  
  <pre><code>CheckDate  CheckIn   CheckOut
  ---------- --------- ---------
  05/23/2011 NULL      17:04:27
  </code></pre>