Mysql – Left join including nulls

MySQLquery

i know this question might be dumb and might be duplicated. but im having a problem with my left join query. so please help Y.Y

the output im currently is something like this

judge name|score
judge1    |87
judge2    |87
judge3    |87
judge4    |87

but the output that i want is with null like something like this

judge name|score
judge1    |87
judge2    |87
judge3    |87
judge4    |87
judge5    |null
judge6    |null

scores are varchar and if table has no record match from table 1 will be automatically should be null or much better for default score of 87.

my query sample

SELECT j.judge_name,s.score 
FROM `tbl_judges` j 
LEFT JOIN tbl_scoring s 
    on j.id = s.jud_id 
where s.crit_id = 1 
  and s.can_id = 11

Best Answer

if table has no record match from table 1 will be automatically should be null 

SELECT j.judge_name, 
       s.score 
FROM tbl_judges j 
LEFT JOIN tbl_scoring s 
       ON j.id = s.jud_id 
      AND s.crit_id = 1 
      AND s.can_id = 11

or much better for default score of 87

SELECT j.judge_name, 
       COALESCE(s.score, 87) score
FROM tbl_judges j 
LEFT JOIN tbl_scoring s 
       ON j.id = s.jud_id 
      AND s.crit_id = 1 
      AND s.can_id = 11
Related Question