Display the data that does not exist in particular table using multiple interconnecting table join

I am trying to list all students that did not enroll in this subject yet using the var_subjectcode as filter. I am using the code below but it is not working properly. It keeps on resulting empty string.

DELIMITER //
DROP PROCEDURE IF EXISTS proc_enrollment//
CREATE PROCEDURE proc_enrollment(IN var_subjectcode VARCHAR(255))
BEGIN
Select CONCAT(a.LastName, ', ', a.FirstName) as StudentNames , a.number
From a
Join b ON a.number = b.number
Join c ON b.number = c.number
Join d ON c.stud_id = d.stud_id
Join e ON d.subject_code = e.subject_code
Where e.subjectcode = var_subjectcode AND a.number is NULL;
END
//DELIMITER ;

If I excluded the AND a.number is NULL in the whole query it returns the list of students who enrolled in that particular var_subjectcode. What I wanted is the opposite. If I put value in the var_subjectcode it should return the list of students who are not enrolled in that var_subjectcode.

var_subjectcode is in the E Table.

Is there a better way to get my desired result?

Answer

if you want list of student who not participated in the given subject code, here is qhat you need also simplified:

Select CONCAT(a.LastName, ', ', a.FirstName) as StudentNames , a.number
From a
Join b ON a.number = b.number
Join c ON b.number = c.number

Join d ON c.stud_id = d.stud_id
where not exists ( select 1 from d where c.stud_id = d.stud_id and d.subject_code =var_subjectcode  )

Advertisement

Answer