Left Join Not Joining Data

Question

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.

ScaisEdge · Accepted Answer · 2016-04-29T15:50:42.887

2

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 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

edited Apr 29 '16 at 15:50

answered Apr 29 '16 at 14:40

ScaisEdge

131,976
10
91
107

Is there a way for the results to be percentage of whole? In other words, can the SELECT statement be composed with percentages in mind? Would love to do in SELECT statement so I don't have to translate using PHP. – user3258571 Apr 29 '16 at 15:07
Wow! Awesome. Thanks for your help. – user3258571 Apr 29 '16 at 15:23
scaisEdge Line 1 has 2 from. Is that correct or an error? – user3258571 Apr 29 '16 at 15:48
I have update the answer ..one from only .. is right – ScaisEdge Apr 29 '16 at 15:51
Also, can you help me have SELECT statement show Ethnicity, % of Count of Ethnicity, % of Count of Ethnicity Total (without WHERE student_Schedule.Course='AS%'). That way my results can show how many with AS% vs total school population. – user3258571 Apr 29 '16 at 15:55
staring from the code i provided you should only remove .. the where condition that is not useful ... i a just a sql magic word .... try , testing, making mistakes you learn... .. – ScaisEdge Apr 29 '16 at 16:04
Could use your help as I believe the Count of Ethnicity Total will require a CASE statement (which I am still learning). – user3258571 Apr 29 '16 at 16:11
Well I don't know your goal so i suggest you of post a proper question (new) with your schema and a sample of the related data and of the expected result too..and a clear explanation of what you are looking for .. this because is not easy understand your goal withot a data scheda and sample data .. Furthermore with a new question all the community could help, not just me .. – ScaisEdge Apr 29 '16 at 16:20

score 0 · Answer 2 · answered Apr 29 '16 at 14:40

You are confusing the LEFT JOIN principle with the RIGHT JOIN. This sentence is wrong: "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)."

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

So to make it work you should use RIGHT JOIN

score 0 · Answer 3 · edited May 23 '17 at 12:31

You misunderstood what LEFT JOIN means. I advise you read about Difference between left join and right join on SO (graphical explanation at it's finest).

Basically the LEFT JOIN includes all rows from the first (left) table of the join, and joins the rows from the second (right) table that match a join condition, or adds NULL columns if there are no matching rows in the second (right) table.

If you want to exclude the rows with no ethnicity info, what you need to do is either:

Do an INNER JOIN (which exludes rows with no match, in your case the students with no student_info)
Do a RIGHT JOIN with added condition WHERE student_info.Ethnicity IS NOT NULL

Either of these will eliminate the "blank" rows, and the COUNT will still be done correctly. Like this (i used [INNER] JOIN) :

SELECT student_info.Ethnicity, COUNT(DISTINCT student_schedule.ID)
FROM student_schedule INNER 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

Left Join Not Joining Data

3 Answers3