user3258571 user3258571 - 7 months ago 21
SQL Question

Left Join Not Joining Data

Trying to JOIN 2 tables to count ethnicity based on 1st table (student_schedule), where student may show up more than 1 time. Table 2 (student_info) just has the student show up 1 time by ID with the student's ethnicity. I am using a LEFT JOIN because on occasion I may have a missing student in the student_info table and they will not be counted (since their ethnicity was not declared).

SELECT student_info.Ethnicity, COUNT(DISTINCT student_schedule.ID)
FROM student_schedule LEFT JOIN student_info ON
student_schedule.ID=student_info.ID
WHERE student_schedule.Course LIKE 'AS%'
GROUP BY student_info.Ethnicity
ORDER BY COUNT(DISTINCT student_schedule.ID) DESC


Looking to display a summary with ethnicity and count:
White 50
Black 25
Hispanic 15
Asian 10

Using my query, result is showing as:
"blank" 60
White 20
Black 15
Hispanic 3
Asian 2

The total students to be counted is correct but the ethnicity does not seem to be joining with Table 1 (student_schedule). Have no idea where the "blank" is coming from. FYI it does not print blank. It just shows as missing with a number next to it. Can someone help to review my query? I must be missing something.

Answer

try using only, do the fact you are group by student_info.ethnicity and check for null

 SELECT ifnull(student_info.Ethnicity, 'not_declared'), COUNT(*)
 FROM student_schedule LEFT JOIN student_info ON     
 student_schedule.ID=student_info.ID
 WHERE student_schedule.Course LIKE 'AS%'
 GROUP BY student_info.Ethnicity
 ORDER BY COUNT(DISTINCT student_schedule.ID) DESC


 SELECT ifnull(student_info.Ethnicity, 'not_declared'), (COUNT(*)/ (select count(*) from FROM student_schedule LEFT JOIN student_info ON     
 student_schedule.ID=student_info.ID
 WHERE student_schedule.Course LIKE 'AS%'))* 100
 FROM student_schedule LEFT JOIN student_info ON     
 student_schedule.ID=student_info.ID
 WHERE student_schedule.Course LIKE 'AS%'
 GROUP BY student_info.Ethnicity
 ORDER BY COUNT(DISTINCT student_schedule.ID) DESC
Comments