sql error 1241 operand should contain 1 column

Question

This is my query

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

It works fine without the pname not in ... clause.

I can't figure out whats wrong cause syntactically it's correct as mysql doesn't show any errors.

Answer 1

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.