Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

1 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.