MySQL Error 1241 – Operand Should Contain 1 Column, INTERSECT Two Subqueries

database-designMySQL

I'm running this query in MYSQL and I'm getting the error 1241: Operand should contain 1 column(s).

I want to know the names and student id's of the students enrolled in Database Management Systems and Knowledge Discovery in Databases (both course titles in the CoursesOffer table)

Students(stuID: String, stuName: String, gender: String, birthdate: Date, enterYear: Year, gpa: Float)
Departments(deptName: String, numPhDs: Integer)
ProfessorWorks(profID: String, profName: String, deptName: String)
CoursesOffer(cNo: String, cTitle: String, deptName: String)
Majors(deptName: String, stuID: String, degreeProgram: String, attendYear: Year, attendSemester: String)
Sections(cNo: String, semester: String, sectNo: String, profID: String)
Enrolls(stuID: String, cNo: String, sectNo: String, semester: String, grade: String)

My query looks like

SELECT S.StuName, S.StuID 
FROM Students S NATURAL JOIN CoursesOffer C NATURAL JOIN Enrolls E 
WHERE cTitle='Database Management Systems'
IN (
SELECT S2.StuName, S2.StuID 
FROM Students S2 NATURAL JOIN CoursesOffer C2 NATURAL JOIN Enrolls E2 
WHERE cTitle='Knowledge Discovery in Databases');

Best Answer

You probably want to replace:

WHERE cTitle='Database Management Systems'
IN (
SELECT S2.StuName, S2.StuID 
...
  ) ;

with:

WHERE cTitle='Database Management Systems'
  AND (S.StuName, S.StuID) IN (                -- this changed
SELECT S2.StuName, S2.StuID 
...
  ) ;

Related Solutions

Mysql – sql error 1241 operand should contain 1 column

You cannot have multiple columns being returned in a subquery like that, so you have several ways that you would have rewrite this query to work.

Either you can unpivot the data in the team table so you are only returning one column:

select * 
from players 
where sport='football' 
  and position='DEF' 
  and pname!='Binoy Dalal' 
  and pname not in (select player1 
                    from team where sap='60003100009'
                    union all
                    select player2
                    from team where sap='60003100009'
                    union all
                    select player3
                    from team where sap='60003100009'
                    union all
                    select player4
                    from team where sap='60003100009'
                    union all
                    select player5
                    from team where sap='60003100009'
                    union all
                    select player6
                    from team where sap='60003100009'
                    union all
                    select player7
                    from team where sap='60003100009'
                    union all
                    select player8
                    from team where sap='60003100009') 
order by price desc;

Or you can use a NOT EXISTS query:

select * 
from players p
where sport='football' 
  and position='DEF' 
  and pname!='Binoy Dalal' 
  and not exists (select *
                  from team t
                  where sap='60003100009' 
                    AND
                    (
                      p.pname = t.player1 OR
                      p.pname = t.player2 OR
                      p.pname = t.player3 OR
                      p.pname = t.player4 OR
                      p.pname = t.player5 OR
                      p.pname = t.player6 OR
                      p.pname = t.player7 OR
                      p.pname = t.player8
                    ))
order by price desc;

Or you would have to use multiple WHERE filters on the player name:

select * 
from players 
where sport='football' 
  and position='DEF' 
  and pname!='Binoy Dalal' 
  and pname not in (select player1 
                    from team where sap='60003100009')
  and pname not in (select player2 
                    from team where sap='60003100009')
  and pname not in (select player3 
                    from team where sap='60003100009')
  and pname not in (select player4 
                    from team where sap='60003100009')
  and pname not in (select player5 
                    from team where sap='60003100009')
  and pname not in (select player6 
                    from team where sap='60003100009')
  and pname not in (select player7 
                    from team where sap='60003100009')
  and pname not in (select player8 
                    from team where sap='60003100009')
order by price desc;

However, ideally you should consider normalizing the team table so you have one column with the player name and another column that assigns them a player number. Similar to this:

create table team
(
  player varchar(50),
  playerNumber int
);

Then when you are searching the team data you only have to join on one column instead of 8 different columns.

MySQL Select Items Where Single Column Contains Multiple Values

This is the relational division problem and there is a question about it at SO, with a lot of ways to write this query, plus performance analysis for PostgreSQL: How to filter SQL results in a has-many-through relation

Shamelessly copying code form there and removing/changing code for answers that have features lacking from MySQL, like CTEs, EXCEPT, INTERSECT, etc, here are a few ways to do this.

Assumptions:

the table is called factors
there is a UNIQUE constraint on (wordid, docid)
there is a documents and a words table:

Easy to write, medium efficiency:

-- Query 1 -- by Martin
SELECT d.docid, d.docname
FROM   document d
JOIN   factors f USING (docid)
WHERE  f.wordid IN (2, 4, 5)
GROUP  BY d.docid
HAVING COUNT(*) = 3 ;           -- number of words

Easy to write, medium efficiency:

-- Query 2 -- by Erwin
SELECT d.docid, d.docname
FROM   documents d
JOIN   (
   SELECT docid
   FROM   factors
   WHERE  wordid IN (2, 4, 5)
   GROUP  BY docid
   HAVING COUNT(*) = 3
   ) f USING (docid) ;

More complex to write, very good efficiency in Postgres - probably lousy in MySQL:

-- Query 4 -- by Derek
SELECT d.docid, d.docname
FROM   documents d
WHERE  d.docid IN (SELECT docid FROM factors WHERE wordid = 2)
AND    d.docid IN (SELECT docid FROM factors WHERE wordid = 4);
AND    d.docid IN (SELECT docid FROM factors WHERE wordid = 5);

More complex to write, very good efficiency in Postgres - and probably the same in MySQL:

-- Query 5 -- by Erwin
SELECT d.docid, d.docname
FROM   documents d
WHERE  EXISTS (SELECT * FROM factors 
               WHERE  docid = d.docid AND wordid = 2)
AND    EXISTS (SELECT * FROM factors 
               WHERE  docid = d.docid AND wordid = 4)
AND    EXISTS (SELECT * FROM factors 
               WHERE  docid = d.docid AND wordid = 5) ;

More complex to write, very good efficiency in Postgres - and probably the same in MySQL:

-- Query 6 -- by Sean
SELECT d.docid, d.docname
FROM   documents d
JOIN   factors x ON d.docid = x.docid
JOIN   factors y ON d.docid = y.docid
JOIN   factors z ON d.docid = z.docid
WHERE  x.wordid = 2
AND    y.wordid = 4
AND    z.wordid = 5 ;

Easy to write and extend to an arbitrary set of words but not as efficient as the JOIN and EXISTS solutions:

-- Query 7 -- by ypercube
SELECT d.docid, d.docname
FROM   documents d
WHERE  NOT EXISTS (
   SELECT *
   FROM   words AS w 
   WHERE  w.wordid IN (2, 4, 5)
   AND    NOT EXISTS (
      SELECT *
      FROM   factors AS f 
      WHERE  f.docid = d.docid 
      AND    f.wordid = w.wordid 
      )
   );

Easy to write, not good efficiency:

-- Query 8 -- by ypercube
SELECT d.docid, d.docname
FROM   documents d
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 2 AS wordid UNION  ALL
      SELECT 4 UNION ALL
      SELECT 5
      ) AS w
   WHERE NOT EXISTS (
      SELECT *
      FROM   factors AS f 
      WHERE  f.docid = d.docid 
      AND    f.wordid = w.wordid 
      )
   );

Enjoy testing them :)

Best Answer

Related Solutions

Mysql – sql error 1241 operand should contain 1 column

MySQL Select Items Where Single Column Contains Multiple Values

Related Question