title: MySQL left join subquery null join
body: <p>Ok, so I have a fairly detailed SQL query, I have 2 tables, the first table contains homework data. The second table contains a record for each student in a class the homework is set for.</p>
  
  <p>I want to count some data on the second table, where certain conditions are met, for example, how many students the homework was issued to, count how many students have returned homework, and count how many homeworks have been reviewed by the teacher.</p>
  
  <p>I'll post my query at the bottom of this post.</p>
  
  <p>I'm using a subquery on 2 left joins. The subquery looks like this:</p>
  
  <pre><code>LEFT JOIN (
      SELECT homework_id, COUNT(uid) AS total_returned
      FROM tbl_homework_student_log 
      WHERE homework_completed = 1
  ) r2 ON r2.homework_id = h.uid
  </code></pre>
  
  <p>In the example above, the student log table contains an index <code>homework_id</code> which links back to the master homework table's unique index <code>h.uid</code>.</p>
  
  <p>My problem is occuring when this scenario is valid:</p>
  
  <p>1) If <code>COUNT(uid)</code> equals 0, homework_id is <code>NULL</code>.</p>
  
  <p>2) The query produces the following result</p>
  
  <pre><code> r2.homework_id   |  total_returned (COUNT(uid))
  ------------------+------------------------------
   NULL             | 0
  </code></pre>
  
  <p>My joins failing with the following error message:</p>
  
  <pre><code>Column 'homework_id' cannot be null - (r2 ON r2.homework_id (null) = h.uid)
  </code></pre>
  
  <p>I don't know how to solve this issue. I initally had my subquery in the SELECT clause, but I want it in the LEFT JOINs.</p>
  
  <p>My question is how can I make sure that <code>r2.homework_id</code> is never null? 
  I've tried the following:</p>
  
  <ul>
  <li>Removed the COUNT() function from the join subqueries and moved them to the main select clause - Doesn't work as expected</li>
  <li>Moved the <code>where</code> clause in each join subquery to its associated <code>ON</code> clause - Doesn't work</li>
  <li>Tried adding <code>AND r2.homework_id IS NOT NULL</code> to the respective <code>ON</code> clause - Doesn't work</li>
  <li>Tried adding <code>AND COUNT(uid)&gt;0</code> to the <code>WHERE</code> clause in the join subquery - Doesn't work</li>
  </ul>
  
  <p>I'm all out of ideas.</p>
  
  <p>Here's my whole query:</p>
  
  <pre><code>SELECT h.uid, h.class_id, h.homework_details, h.require_upload_return,
         CONCAT(u.surname, ', ', u.forename) AS teacher_name, 
         DATE_FORMAT(h.set_date, '%D %M %Y') AS set_date_DMY,
         DATE_FORMAT(h.set_date, '%b %e, %Y') AS set_date_beY,
         UNIX_TIMESTAMP(h.set_date) AS set_date_timestamp,
         DATE_FORMAT(h.due_date, '%D %M %Y') AS due_date_DMY, 
         DATE_FORMAT(h.due_date, '%b %e, %Y') AS due_date_beY,
         UNIX_TIMESTAMP(h.due_date) AS due_date_timestamp, 
         IF(h.due_date&lt;=DATE(NOW()), 1, 0) AS homework_due, 
         r1.total_issues, 
         IF(r2.total_returned IS NULL, 0, r2.total_returned) AS total_returned, 
         IF(h.due_date&lt;=DATE(NOW()), r1.total_issues, IF(r2.total_returned IS NULL, 0, r2.total_returned)) AS waiting_review,
         h.resource_file
  FROM tbl_homework h
  INNER JOIN tbl_users u 
      ON u.uid = h.teacher_id
  INNER JOIN (
      SELECT homework_id, COUNT(uid) AS total_issues 
      FROM tbl_homework_student_log
  ) r1 ON r1.homework_id = h.uid
  LEFT JOIN (
      SELECT uid, homework_id, COUNT(uid) total_returned 
      FROM tbl_homework_student_log
      WHERE homework_completed = 1
  ) r2 ON r2.homework_id = h.uid 
  LEFT JOIN (
      SELECT homework_id, COUNT(uid) waiting_review 
      FROM tbl_homework_student_log
      WHERE seen_by_issuer = 0
  ) r3 ON r3.homework_id = h.uid
  WHERE h.teacher_id = ?
      AND h.set_date&gt;=DATE_SUB(NOW(), INTERVAL 10 DAY) 
      AND h.homework_template = 0
  ORDER BY h.class_id ASC, 
      h.set_date ASC
  </code></pre>