I use the follown as one movie's page Code: SELECT `mt_films`.`Title1` AS `Title1`, GROUP_CONCAT(DISTINCT `t_idiotita`.`idiotita`, ': ', `t_people`.`person` ORDER BY `t_idiotita`.`cnt_gr_id` ASC SEPARATOR ' ') AS `kati` FROM ((`t_people` JOIN (`st_peoplefilms` JOIN `mt_films` ON ((`mt_films`.`ID_films` = `st_peoplefilms`.`ID_films`))) ON ((`t_people`.`ID_person` = `st_peoplefilms`.`ID_person`))) JOIN `t_idiotita` ON ((`st_peoplefilms`.`ID_idiotita` = `t_idiotita`.`ID_idiotita`))) WHERE ((`st_peoplefilms`.`ID_idiotita` = 29) OR (`st_peoplefilms`.`ID_idiotita` = 33) OR (`st_peoplefilms`.`ID_idiotita` = 27) OR (`st_peoplefilms`.`ID_idiotita` = 12) OR (`st_peoplefilms`.`ID_idiotita` = 43) OR (`st_peoplefilms`.`ID_idiotita` = 11) OR (`st_peoplefilms`.`ID_idiotita` = 32) OR (`st_peoplefilms`.`ID_idiotita` = 5) OR (`st_peoplefilms`.`ID_idiotita` = 24) OR (`st_peoplefilms`.`ID_idiotita` = 14)) GROUP BY `mt_films`.`Title1` That returns e.g. Title1 | Dir.: John | Script: Peter | Script: Anna | ... Title2 | Dir.: George | Dir.: Paul | Script: Gus | ... But while for many many movies work more than one person for the same specialty i need a second group_concat to group all the persons for the same specilty. I also need two different separations. The first ": " separates the specialties and the second ", " the persons Expected result Title1 | Dir.: John | Script: Peter, Anna | ... Title2 | Dir.: George, Paul | Script: Gus | ... Could you pls help? Thank's
Give you an example here: Code: select a,group_concat(concat(b,' ',c)) from (select emp_dept as a,emp_grade as b,group_concat(distinct emp_sex SEPARATOR ' ') as c from emp_small where emp_dept<'B' group by emp_dept,emp_grade) dt group by a ; subquery result: 'AAA', '1000', 'F M' 'AAA', '1001', 'M' 'AAA', '1003', 'M' 'AAA', '1005', 'M' 'AAA', '1006', 'F' 'AAA', '1012', 'M' 'AAA', '1014', 'F M' 'AAA', '1020', 'F' 'AAA', '1025', 'F' 'AAA', '1027', 'F' 'AAA', '1029', 'M' Final result: 'AAA', '1000 F M,1001 M,1003 M,1005 M,1006 F,1012 M,1014 F M,1020 F,1025 F,1027 F,1029 M 'ABC', '1001 F,1002 F,1011 M,1012 F,1013 M,1027 M,1029 M,1033 M,1040 F,1049 M,1050 F,1052 M ....
I try my best to understand your SQL, you may try this, the key factor is that whether the grouping of `t_idiotita`.`cnt_gr_id` is applicable, it is up to your judgement. SELECT dt.`Title1` AS `Title1`, GROUP_CONCAT(DISTINCT dt.`idiotita`, ': ', dt.`person` ORDER BY dt.`cnt_gr_id` ASC SEPARATOR ' ') AS `kati` from (SELECT `mt_films`.`Title1` AS `Title1`, `t_idiotita`.`idiotita` as idiotita, `t_idiotita`.`cnt_gr_id` as `cnt_gr_id` GROUP_CONCAT(DISTINCT`t_people`.`person` SEPARATOR ',') AS `persons` FROM ... ....... group by `mt_films`.`Title1`,`t_idiotita`.`idiotita`,`t_idiotita`.`cnt_gr_id`) dt GROUP BY dt.`Title1`