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

Question

How to fix this mysql query

SELECT no, name,
(SELECT chapter, max FROM table2 WHERE name = user.name AND max = 10) as 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,

Answer 1

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.