Whats wrong with this SQL? 2

[pgaec]

MySQL for some reason is not happy with this :

select * from cr_person where id in  (
	select person1,pereson2,person3,person4,person5,person6 from cr_team where teamid = 1 
)

Executing

	select person1,pereson2,person3,person4,person5,person6 from cr_team where teamid = 1

gives me 1,2,4,5,6,9

and executing

select * from cr_person where id in  (
1,2,4,5,6,9
)

seems to be fine too.

But when I put them together, thats when MySQL starts complaining.

select * from cr_person where id in  (
	select person1,pereson2,person3,person4,person5,person6 from cr_team where teamid = 1 
)

Any ideas?

 

[snowboardr]

i am not sure that i can help you but im sure if you post an error it will help whoever can... because i don't see a problem with that one...

 

[KarveR]

What version of mysql, earlier 4x versions dont support subqueries.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

used in an IN list, the subquery must return no more than a single column

this should work --

select * 
  from cr_person 
 where id 
    in ( 
       select person1
         from cr_team 
        where teamid = 1
       union 
       select person2
         from cr_team 
        where teamid = 1
       union 
       select person3
         from cr_team 
        where teamid = 1
       union 
       select person4
         from cr_team 
        where teamid = 1
       union 
       select person5
         from cr_team 
        where teamid = 1
       union 
       select person6
         from cr_team 
        where teamid = 1
       )

r937.com | rudy.ca

 

[pgaec]

thanks for that. that worked!