Get results when where value is 'grade' or where it doesn't exist

Question

I have a wordpress site that is used to store student grades on various lessons (from the quizzes on the site). I am trying to create a query that will pull out all of the lessons for all students in a certain group (using buddypress groups) and the students grades in each lesson.

I have created this query:

SELECT u.display_name, p.post_title, cm.meta_value
FROM wp_users u
JOIN wp_bp_groups_members gm
    ON u.ID = gm.user_id
JOIN wp_comments c
    ON u.ID = c.user_id
JOIN wp_commentmeta cm
    ON c.comment_ID = cm.comment_id
JOIN (SELECT * FROM wp_posts WHERE post_type LIKE 'lesson') p
    ON c.comment_post_id = p.ID
WHERE gm.group_id = 4 AND cm.meta_key LIKE 'grade'

This currently returns all the grades for all students in a group in the lessons they have attempted the test. However it does not return any lessons they have not attempted the test in, which I need still.

Just to be clear: lessons are posts, grades are meta_values in a record with a meta_key of 'grades'. These are stored as comments, and comment_meta.

I hope this is all clear and you can help. Thanks.

After Ollie Jones help I made this:

SELECT u.display_name, p.post_title, IFNULL(cm.meta_value,'--nothing--') grade
FROM wp_users u
JOIN wp_comments c
LEFT JOIN wp_commentmeta cm
    ON c.comment_ID = cm.comment_id AND cm.meta_key = 'grade'
JOIN wp_bp_groups_members gm
    ON u.ID = gm.user_id
JOIN wp_posts p
    ON c.comment_post_id = p.ID
WHERE gm.group_id = 4 AND p.post_type LIKE 'lesson'

Which almost works but returns all student grades, not just the ones in the group (though it only gives the one name of the student in the group).

Answer 1

This is the familiar key/value store problem, that comes up with commentmeta, postsmeta, and usermeta. When you JOIN two tables and the left one might not have a corresponding row, you need to use LEFT JOIN. When the left one is a key/value table, you need to adjust the ON condition accordingly. Finally, when you LEFT JOIN two tables and there's no matching row in it it, you get back NULLs for those columns, so you must allow for that.

So this kind of SQL pattern will do the trick for you

SELECT whatever, IFNULL(cm.meta_value,'--nothing--') grade
  FROM whatever
  LEFT JOIN wp_comments c ON whatever.id = c.user_id
  LEFT JOIN wp_commentmeta cm ON c.comment_id = cm.comment_id
                             AND cm.meta_key = 'grade'
  JOIN whatever