Take the 2-minute tour ×
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.

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.

share|improve this question
add comment

1 Answer

up vote 7 down vote accepted

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.

share|improve this answer
2  
Extra +1 for the suggestion to normalize the table. –  mdoyle Jan 25 '13 at 15:02
    
thanks a ton....works like a charm i cant normalize this table now but i'll keep it in mind, next time i am working on something similar –  Binoy Dalal Jan 26 '13 at 9:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.