CODEWITHSUNDEEP
mysql
Francisunoxx
Francisunoxx

Reputation: 1458

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)

Upvotes: 1

Views: 42

Answers (1)

Yuda Prawira
Yuda Prawira

Reputation: 12481

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;

Upvotes: 1

Related Questions