Francisunoxx Francisunoxx - 11 months ago 37
MySQL Question

Using NOT IN and NOT at the same time

I'm trying to filter all the with or without grades of the student using method

NOT IN
and
IN
. I used
NOT IN
when selecting empty grades for student while for
IN
I select the students with grade. Is it possible to put them all in one query instead of two query? Because I wanted to select them all the same time then calling somewhere in my class.

Query for
IN
:

SELECT subject_mt.subject_id, student_mt.student_id,
registration_mt.firstname, registration_mt.middlename, registration_mt.lastname,
subject_mt.title,
MAX(IF(g.gradingperiod_id = 7000, g.grade, ""))AS first,
MAX(IF(g.gradingperiod_id = 7001, g.grade, "")) AS second,
MAX(IF(g.gradingperiod_id = 7002, g.grade, "")) AS third,
MAX(IF(g.gradingperiod_id = 7003, g.grade, "")) AS fourth,
g.final

FROM faculty_schedule
INNER JOIN schedule_mt ON schedule_mt.schedule_id = faculty_schedule.schedule_id
INNER JOIN section_mt ON section_mt.section_id = schedule_mt.section_id
INNER JOIN section_student ON section_student.section_id = section_mt.section_id
INNER JOIN student_mt ON student_mt.student_id = section_student.student_id
INNER JOIN registration_mt ON registration_mt.registration_id = student_mt.registration_id
INNER JOIN subject_mt ON subject_mt.subject_id = schedule_mt.subject_id
INNER JOIN student_grade AS sg ON sg.student_id = student_mt.student_id
INNER JOIN grade AS g ON g.grade_id = sg.grade_id


//WHERE CLAUSE HERE

GROUP BY
subject_mt.subject_id, student_mt.student_id,
registration_mt.firstname, registration_mt.middlename, registration_mt.lastname,
subject_mt.title,
g.final;


Query for
NOT IN
:

INNER JOIN student_grade AS sg ON sg.student_id = student_mt.student_id
INNER JOIN grade AS g ON g.grade_id = sg.grade_id

WHERE faculty_schedule.faculty_id = pIN_facultyId
AND schedule_mt.section_id = pIN_sectionId
AND sg.student_id IN (SELECT student_id FROM student_grade)

Answer Source

So I am trying to close my answer here beside my comment. I think the query would be look like this using LEFT JOIN which is you need to filter all data with or without grades.

SELECT subject_mt.subject_id, student_mt.student_id,
   registration_mt.firstname, registration_mt.middlename, registration_mt.lastname,
   subject_mt.title,
   MAX(IF(g.gradingperiod_id = 7000, g.grade, ""))AS first,
   MAX(IF(g.gradingperiod_id = 7001, g.grade, "")) AS second,
   MAX(IF(g.gradingperiod_id = 7002, g.grade, "")) AS third,
   MAX(IF(g.gradingperiod_id = 7003, g.grade, "")) AS fourth,
   g.final

FROM faculty_schedule
INNER JOIN schedule_mt ON schedule_mt.schedule_id = faculty_schedule.schedule_id
INNER JOIN section_mt ON section_mt.section_id = schedule_mt.section_id
INNER JOIN section_student ON section_student.section_id = section_mt.section_id
INNER JOIN student_mt ON student_mt.student_id = section_student.student_id
INNER JOIN registration_mt ON registration_mt.registration_id = student_mt.registration_id
INNER JOIN subject_mt ON subject_mt.subject_id = schedule_mt.subject_id
LEFT JOIN student_grade AS sg ON sg.student_id = student_mt.student_id
LEFT JOIN grade AS g ON g.grade_id = sg.grade_id

WHERE faculty_schedule.faculty_id = pIN_facultyId
AND schedule_mt.section_id = pIN_sectionId
AND sg.student_id IN (SELECT student_id FROM student_grade)

GROUP BY 
subject_mt.subject_id, student_mt.student_id,
registration_mt.firstname, registration_mt.middlename, registration_mt.lastname,
subject_mt.title,
g.final;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download