MySQL: JOIN syntax + selects within selects = Operand Error

Question

I've been wrestling with this query for a while. Here it is:

select First10.mal, Last10.family_name,
(select * from gender2
JOIN
(select * from status) as Mix1
JOIN
(select * from age as Mix2 order by rand() limit 10) as Mix3
JOIN
(select incidentid from incidentid2 as Mix4)
as Mix5 where data='mal' and incidentid='6' and status IN ('inj','ali') and age IN ('NULL','0-17') 
order by rand() limit 100)
from ( select fn.mal, @fns := @fns + 1 as Sequence
from ( select mal from fnames where mal IS NOT NULL order by rand() limit 100) fn,
(select @fns := 0 ) vars ) First10
JOIN
( select ln.family_name, @lns := @lns + 1 as Sequence
from ( select family_name from lastnames order by rand() limit 100 ) ln,
(select @lns := 0 ) vars ) Last10
ON First10.Sequence = Last10.Sequence;

My goal here is to insert into a table which would present a randomized first name, last name, gender, status, age, and incident id. I have tried many ways to rework this script, including seperating the select statements, but I always seem to end up with this error:

ERROR 1241 (21000): Operand should contain 1 column(s)

Please advise guys, this has been stressing me out for a while now... It's probably a very simple answer, but I am just confusing myself further. If you need any clarifications, just ask.

score 0 · Accepted Answer · answered Jul 15 '11 at 12:13

Well, it looks like after a good night's sleep and some help from a friend, I got this query working. For those that are looking for an answer to a similar question, here is how I got this to work:

select mal,family_name,data,age,status,incidentid
from ( select fn.mal, @fns := @fns + 1 as Sequence
from ( select mal from fnames where mal IS NOT NULL order by rand() limit 100) fn,
(select @fns := 0 ) vars ) as FN
INNER JOIN
(select ln.family_name, @lns := @lns + 1 as Sequence
from ( select family_name from lastnames order by rand() limit 100 ) ln,
(select @lns := 0 ) vars ) as LN
INNER JOIN
(select * from gender2) as Mix0
INNER JOIN
(select * from status) as Mix1
INNER JOIN
(select * from age as Mix2 order by rand() limit 3) as Mix3
INNER JOIN
(select incidentid from incidentid2 as Mix4)
as Mix5 where data='mal' and incidentid='6' and status IN ('inj','ali') and age IN ('NULL','0-17') 
order by rand() limit 100;

MySQL: JOIN syntax + selects within selects = Operand Error

1 Answers1