example GROUP_CONCAT

Question

I have a table with two columns (case and subcase) and the following values:

case subcase
1    0
2    1
3    1
4    0
5    0
6    4

I want to have a list of cases only having subcase(s) like:

case  list_of_subcase
1     2,3
4     6
5     0

Cases 2,3,and 6 are not listed because they are subcases.

Case 5 have no subcases but is not subcase itself.

How to write proper select? Any help?

Answer 1

Are we in danger of overthinking this...

DROP TABLE my_table;

CREATE TABLE my_table
(my_case INT NOT NULL
,subcase INT NULL
);

INSERT INTO my_table VALUES
(1    ,NULL),
(2    ,1),
(3    ,1),
(4    ,NULL),
(5    ,NULL),
(6    ,4);

SELECT x.my_case
     , GROUP_CONCAT(y.my_case) subcases 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.subcase = x.my_case 
 WHERE x.subcase IS NULL 
 GROUP 
    BY x.my_case;
+---------+----------+
| my_case | subcases |
+---------+----------+
|       1 | 2,3      |
|       4 | 6        |
|       5 | NULL     |
+---------+----------+