MySQL left join subquery null join

Question

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.

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.

I'll post my query at the bottom of this post.

I'm using a subquery on 2 left joins. The subquery looks like this:

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

In the example above, the student log table contains an index homework_id which links back to the master homework table's unique index h.uid.

My problem is occuring when this scenario is valid:

1) If COUNT(uid) equals 0, homework_id is NULL.

2) The query produces the following result

 r2.homework_id   |  total_returned (COUNT(uid))
------------------+------------------------------
 NULL             | 0

My joins failing with the following error message:

Column 'homework_id' cannot be null - (r2 ON r2.homework_id (null) = h.uid)

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.

My question is how can I make sure that r2.homework_id is never null? I've tried the following:

• Removed the COUNT() function from the join subqueries and moved them to the main select clause - Doesn't work as expected
• Moved the where clause in each join subquery to its associated ON clause - Doesn't work
• Tried adding AND r2.homework_id IS NOT NULL to the respective ON clause - Doesn't work
• Tried adding AND COUNT(uid)>0 to the WHERE clause in the join subquery - Doesn't work

I'm all out of ideas.

Here's my whole query:

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<=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<=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>=DATE_SUB(NOW(), INTERVAL 10 DAY) 
    AND h.homework_template = 0
ORDER BY h.class_id ASC, 
    h.set_date ASC

Answer 1

You don't really need sub-queries, much less three of them to get the data you're looking for. I believe the following will do the trick:

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<=DATE(NOW()), 1, 0) AS homework_due, 
       COUNT(*) AS total_issues, 
       SUM(IF(l.homework_completed, 1, 0)) AS total_returned,
       SUM(IF(l.seen_by_issuer = 0, 1, 0)) AS waiting_review,
       h.resource_file
FROM tbl_homework h
    INNER JOIN tbl_users u ON u.uid = h.teacher_id
    INNER JOIN tbl_homework_student_log l ON h.uid = l.homework_id
WHERE h.teacher_id = ?
    AND h.set_date>=DATE_SUB(NOW(), INTERVAL 10 DAY) 
    AND h.homework_template = 0
GROUP BY h.uid
ORDER BY h.class_id ASC, 
    h.set_date ASC

Answer 2

The main problem is that you're missing GROUP BY clauses in your subqueries, so you're counting everything in the table that meets the WHERE clauses, not counting them per-student. Also, all those tbl_homework_student_log subqueries can be combined into a single query.

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<=DATE(NOW()), 1, 0) AS homework_due, 
       r.total_issues, 
       IFNULL(r.total_returned, 0) AS total_returned, 
       IF(h.due_date<=DATE(NOW()), r.total_issues, IFNULL(r.waiting_review, 0)) 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(*) AS total_issues
            SUM(homework_completed = 1) AS total_returned,
            SUM(seen_by_issuer = 0) AS waiting_review
     FROM tbl_homework_student_log
     GROUP BY homework_id
) r ON r.homework_id = h.uid
WHERE h.teacher_id = ?
    AND h.set_date>=DATE_SUB(NOW(), INTERVAL 10 DAY) 
    AND h.homework_template = 0
ORDER BY h.class_id ASC, 
    h.set_date ASC