as a field in a [mysql select query] to get set of array data

How to fix this mysql query

SELECT no, name,
(SELECT chapter, max FROM table2 WHERE name = user.name AND max = 10) 
  as开发者_Python百科 sub_array1,
(SELECT chapter, max FROM table2 WHERE name = user.name AND max = 20) 
  as sub_array2
FROM user ORDER by exp DESC

example expected out result:

the current query returns me Operand should contain 1 column(s)

basically i want to create something like this:

------------------------------------------------------------------------------
no    | name    |  sub_array1
------------------------------------------------------------------------------
1     |myname   |  sub_array1[0][chapter]=chapter_1,  sub_array1[0][max]=100
      |         |  sub_array1[1][chapter]=chapter_2,  sub_array1[1][max]=70
      |         |  ...
------------------------------------------------------------------------------
2     |myname_2 |  sub_array1[0][chapter]=chapter_1,  sub_array1[0][max]=100
      |         |  sub_array1[1][chapter]=chapter_2,  sub_array1[1][max]=50
      |         |  sub_array1[2][chapter]=chapter_3,  sub_array1[2][max]=60

Actual query

SELECT 
  no, name, maxcombo, exp, level, location,
  (
    (SELECT chapter, MAX(score) as max, name 
     FROM chapter_test_progress 
     WHERE name = user.name AND type = 'vocabulary' GROUP BY chapter
    )
  ) as user_chapter_test_statuses,
  (
    (SELECT chapter, MAX(score) as max, name 
     FROM chapter_test_progress 
     WHERE name = user.name AND type = 'kanji' GROUP BY chapter
    )
  ) as user_chapter_test_status_kanjis
FROM 
  user 
ORDER by 
  exp DESC 
LIMIT $offset, $rowPerPage

Thank You,

I don't know if I correctly understand the structure of your database, but this should do it:

SELECT U.no, U.name, S.chapter, S.max
FROM user U,
         (SELECT CP.chapter, MAX(CP.score) max, CP.name FROM chapter_test_progress CP GROUP BY name, chapter) S
WHERE U.name = S.name
GROUP BY U.name, S.chapter;

The test data I've used:

CREATE TABLE `chapter_test_progress` (
  `chapter` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert  into `chapter_test_progress`(`chapter`,`score`,`name`) values ('ch_1',10,'alice'),('ch_1',20,'alice'),('ch_2',10,'bob'),('ch_2',50,'bob'),('ch_1',100,'alice'),('ch_2',100,'alice'),('ch_1',200,'bob'),('ch_2',200,'bob');

CREATE TABLE `user` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`no`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

insert  into `user`(`no`,`name`) values (1,'alice'),(2,'bob');

You should normalize your database.