0

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)

Improve this question
2
  • 3
    Change SELECT * to id in WHERE NOT IN clouse... Commented May 4, 2016 at 6:43
  • is it working for you? Commented May 4, 2016 at 6:45

3 Answers 3

Reset to default
2

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 )
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

1 Comment

I have Accepted it then Vote me up.
1

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.

Improve this answer

1 Comment

Very helpful idea. Thanks.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.