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.
Best Answer
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:Or you can use a
NOT EXISTS
query:Or you would have to use multiple
WHERE
filters on the player name: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:Then when you are searching the team data you only have to join on one column instead of 8 different columns.