One table is storing a parent/ child relationship. I'm trying to get a return where the entries parent_id returns the parents name child's name as a new column. The structure is as follows:
Table structure
| label_template_category_id | name | parent_id | sort_order |
|---|---|---|---|
| 5 | 'Christmas' | 0 | 4 |
| 7 | 'Father's Day' | 34 | 6 |
| 9 | 'Mother's Day' | 34 | 8 |
| 10 | 'New Baby' | 34 | 9 |
| 11 | 'New Home' | 34 | 10 |
| 13 | 'Thank You' | 0 | 12 |
| 14 | 'Wedding' | 0 | 13 |
| 15 | 'Business' | 0 | 0 |
| 16 | 'Valentine's Day' | 34 | 0 |
| 26 | 'New Year' | 0 | 0 |
| 28 | 'Retirement' | 0 | 0 |
| 29 | 'Sports Events/ Teams' | 0 | 0 |
| 34 | 'Celebration Days' | 0 | 0 |
Expected Results
| label_template_category_id | name | parent_id | sort_order | group_name |
|---|---|---|---|---|
| 5 | 'Christmas' | 0 | 4 | 'Christmas' |
| 7 | 'Father's Day' | 34 | 6 | 'Celebration Days > Father's Day' |
| 9 | 'Mother's Day' | 34 | 8 | 'Celebration Days > Mother's Day' |
| 10 | 'New Baby' | 34 | 9 | 'Celebration Days > New Baby' |
| 11 | 'New Home' | 34 | 10 | 'Celebration Days > New Home' |
| 13 | 'Thank You' | 0 | 12 | 'Thank You' |
| 14 | 'Wedding' | 0 | 13 | 'Wedding' |
| 15 | 'Business' | 0 | 0 | 'Business' |
| 16 | 'Valentine's Day' | 34 | 0 | 'Celebration Days > Valentine's Day' |
| 26 | 'New Year' | 0 | 0 | 'New Year' |
| 28 | 'Retirement' | 0 | 0 | 'Retirement' |
| 29 | 'Sports Events/ Teams' | 0 | 0 | 'Sports Events/ Teams' |
| 34 | 'Celebration Days' | 0 | 0 | 'Celebration Days' |
Using the following query I'm getting all the children returning on the parent instead of on the child like the expected results above.
SELECT
parent.label_template_category_id,
parent.name,
GROUP_CONCAT(child.name SEPARATOR ' > ') group_name
FROM label_template_category parent
LEFT JOIN label_template_category child
ON (child.parent_id = parent.label_template_category_id)
GROUP BY parent.label_template_category_id
ORDER BY group_name
Actual returned results
| label_template_category_id | name | parent_id | sort_order | group_name |
|---|---|---|---|---|
| 14 | 'Wedding' | 0 | 13 | |
| 15 | 'Business' | 0 | 0 | |
| 16 | 'Valentine's Day' | 34 | 0 | |
| 34 | 'Celebration Days' | 0 | 0 | 'Father's Day > Mother's Day > New Baby > New Home > Valentine's Day' |
I've created an SQL Fiddle for testing.
CodePudding user response：
I don't think you need a GROUP_CONCAT() for this. And the order of LEFT JOIN seems the other way around. Try this query:
SELECT
child.label_template_category_id,
child.name,
child.parent_id,
child.sort_order,
CASE WHEN parent.name IS NULL THEN child.name ELSE
CONCAT_WS(' > ',parent.name, child.name) END AS group_name
/*or maybe CONCAT_WS(' > ',parent.name, child.name) END AS group_name */
FROM label_template_category child
LEFT JOIN label_template_category parent
ON (child.parent_id = parent.label_template_category_id);
You need a CASE statement with CONCAT_WS() function. As you can see, I switched the LEFT JOIN where the child is set as reference instead. Then I'm doing a CASE when the result from parent is NULL it will return the data in column child.name. Otherwise, if there's a match, it will concatenate parent.name with child.name.