Lucio Flores
2021-03-15T00:58:24Z

Group_Concat.sql

| CREATE TABLE IF NOT EXISTS `affiliates` (
| `affiliate_id` int(6) unsigned NOT NULL,
| `parent_affiliate_id` int(3) unsigned NOT NULL,
| PRIMARY KEY (`affiliate_id`)
| ) DEFAULT CHARSET=utf8;
|
| INSERT INTO `affiliates` (`affiliate_id`, `parent_affiliate_id`) VALUES
| ('1', '1'),
| ('2', '1'),
| ('3', '2'),
| ('4', '3'),
| ('5', '3'),
| ('6', '3'),
| ('7', '3'),
| ('8', '3'),
| ('9', '1');
|
|
| SELECT affiliate_id, (SELECT GROUP_CONCAT(affiliate_id) from affiliates B where B.parent_affiliate_id = A.affiliate_id) AS all_parent_id FROM affiliates A;
|
| -- Result:
| --
| **Schema (MySQL v5.7)**
|
| CREATE TABLE IF NOT EXISTS `affiliates` (
| `affiliate_id` int(6) unsigned NOT NULL,
| `parent_affiliate_id` int(3) unsigned NOT NULL,
| PRIMARY KEY (`affiliate_id`)
| ) DEFAULT CHARSET=utf8;
|
| INSERT INTO `affiliates` (`affiliate_id`, `parent_affiliate_id`) VALUES
| ('1', '1'),
| ('2', '1'),
| ('3', '2'),
| ('4', '3'),
| ('5', '3'),
| ('6', '3'),
| ('7', '3'),
| ('8', '3'),
| ('9', '1');
|
|
| ---
|
| **Query #1**
|
| SELECT affiliate_id, (SELECT GROUP_CONCAT(affiliate_id) from affiliates B where B.parent_affiliate_id = A.affiliate_id) AS all_parent_id FROM affiliates A;
|
| | affiliate_id | all_parent_id |
| | ------------ | ------------- |
| | 1 | 1,2,9 |
| | 2 | 3 |
| | 3 | 4,5,6,7,8 |
| | 4 | |
| | 5 | |
| | 6 | |
| | 7 | |
| | 8 | |
| | 9 | |
|
| ---
|
| [View on DB Fiddle](https://www.db-fiddle.com/f/5WRvcMBwbwCJhTtpX9rt4v/2)
|
|