[SOLVED] Parent child GROUP_CONCAT on same table joining on wrong reference

Issue

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.

Solution

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.

Demo fiddle

Answered By – FanoFN

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.