Nur Selam Nur Selam - 7 months ago 14
SQL Question

Issue of Select in MySql using NOT IN operator?

I have used the following mysql query to select all selected information where the student id of student table is not exist inside costsharing table but I have encountered an error.

Any help? Thanks in Advance!

select st.id as 'Student ID', st.stud_fname as 'First Name', st.stud_lname as 'Last Name', st.stud_middle_name as 'Middle Name',
dp.dep_name as 'Department',dp.max_dur_year as 'Max Duration', st.entry_year as 'Entry',MAX(sc.acc_year) as 'Current Academic Year',
sum(sc.Tuition_fee+sc.Accomod_fee+sc.Food_fee) as 'Total Cost Sharing'

from student st left JOIN student_costsharing sc on st.id = sc.stud_id
left join department dp on st.dep_id=dp.id

where st.id not in ( SELECT *
FROM student_costsharing
WHERE sc.stud_id=st.id
)

GROUP BY st.stud_fname
order by st.stud_fname


The error Message:


1241 - Operand should contain 1 column(s)

Answer

Try these , I have change the WHERE NOT IN statement :-

select st.id as 'Student ID', st.stud_fname as 'First Name', st.stud_lname as 'Last Name', st.stud_middle_name as 'Middle Name',
 dp.dep_name as 'Department',dp.max_dur_year as 'Max Duration', st.entry_year as 'Entry',MAX(sc.acc_year) as 'Current Academic Year',  
 sum(sc.Tuition_fee+sc.Accomod_fee+sc.Food_fee) as 'Total Cost Sharing'

from student st left JOIN student_costsharing sc on st.id = sc.stud_id
left join department dp on st.dep_id=dp.id 

 where st.id not in ( SELECT stud_id              
        FROM student_costsharing 
        WHERE sc.stud_id=st.id
               )

GROUP BY st.stud_fname
order by st.stud_fname