CODEWITHSUNDEEP
mysql
Nur Selam
Nur Selam

Reputation: 471

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)

Upvotes: 0

Views: 42

Answers (3)

therufa
therufa

Reputation: 2041

The problem is, that the result set of the subquery should contain only one column, since you cant compare the operand of the where clause with an entire result set of a table. Which column should compared to the id?

To solve this, you need to select the one column of the table you want to compare your value with.

Upvotes: 1

Harsh Sanghani
Harsh Sanghani

Reputation: 1712

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

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

Instead of using * you need to specify the column in IN clause

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

Upvotes: 2

Related Questions