How do I fix Error Code 1241: Operand should contain 1 column(s) in mysql query?

487
November 10, 2019, at 7:10 PM
select firstName, lastName from students, courses, registration
where students.studentID = registration.studentID 
and courses.courseCode = registration.courseCode 
and gender = 'M' and courseName = 'Database Systems' 
in(select firstName, lastName 
from students, courses, registration
where students.studentID = registration.studentID 
and courses.courseCode = registration.courseCode 
and gender = 'M' and courseName = 'C++');``

I need to find the male students who have taken both Database Systems and C++, to do this I need to left join the tables students, registration, and courses,

Answer 1

Your in clause is missing some column try

select 
   firstName, lastName 
from students, courses, registration
where students.studentID = registration.studentID 
and courses.courseCode = registration.courseCode 
and gender = 'M' and courseName = 'Database Systems' 
and students.studentID
in (select studentID 
from students, courses, registration
where students.studentID = registration.studentID 
and courses.courseCode = registration.courseCode 
and gender = 'M' and courseName = 'C++');
Answer 2

Your query fails because of the way you use the in operator, which requires a column name or an expression in the left side.

Based on your description of your goal, I suspect that your query could be rewritten to use exists condition with correlated subqueries for filtering, like so:

select 
    firstName, 
    lastName 
from students s
where 
    gender = 'M'
    and exists(
        select 1
        from courses c
        inner join registration r on c.courseCode = r.courseCode 
        where 
            c.courseName = 'Database Systems' 
            and s.studentID = r.studentID 
    )
    and exists(
        select 1
        from courses c
        inner join registration r on c.courseCode = r.courseCode 
        where 
            c.courseName = 'C++' 
            and s.studentID = r.studentID 
    )   

Another possible solution would be to use aggregation, with a having clause for filtering:

select s.firstName, s.lastName 
from students s
inner join registration r 
    on s.studentID = r.studentID 
inner join courses c 
    on  c.courseCode = r.courseCode 
    and c.courseName in ('Database Systems',  'C++' )
where s.gender = 'M'
group by s.studentID, s.firstName, s.lastName 
having count(distinct c.courseName) = 2
Answer 3

This query can be more easily written using a HAVING clause to check that the count of courses that the student has taken out of the set ('Database Systems', 'C++') is 2:

SELECT s.studentID, s.firstName, s.lastName 
FROM students s
JOIN registration r ON s.studentID = r.studentID 
JOIN courses c ON c.courseCode = r.courseCode 
WHERE s.gender = 'M' AND c.courseName IN ('Database Systems', 'C++')
GROUP BY s.studentID, s.firstName, s.lastName
HAVING COUNT(DISTINCT c.courseName) = 2

Note I have rewritten your JOINs in the preferred style with ON conditions.

Rent Charter Buses Company
READ ALSO
Speed ​query update mysql [on hold]

Speed ​query update mysql [on hold]

I have to update about 400 records of a table placed on an online mysql DB containing about 1500 recordsI executed a cycle with an UPDATE query at each cycle, but the update times are about 1 minute

119
Problem with comparison within a trigger statement using a variable gotten from query

Problem with comparison within a trigger statement using a variable gotten from query

I am trying to use an attribute from a 2nd table in the trigger of the 1stTo do this I am trying to load that value into a variable and then use it as a comparison

122
mysql php query getting undefined index error [duplicate]

mysql php query getting undefined index error [duplicate]

This question already has an answer here:

122