Ad

Parent Child GROUP_CONCAT On Same Table Joining On Wrong Reference

- 1 answer

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_idnameparent_idsort_order
5'Christmas'04
7'Father's Day'346
9'Mother's Day'348
10'New Baby'349
11'New Home'3410
13'Thank You'012
14'Wedding'013
15'Business'00
16'Valentine's Day'340
26'New Year'00
28'Retirement'00
29'Sports Events/ Teams'00
34'Celebration Days'00

Expected Results

label_template_category_idnameparent_idsort_ordergroup_name
5'Christmas'04'Christmas'
7'Father's Day'346'Celebration Days > Father's Day'
9'Mother's Day'348'Celebration Days > Mother's Day'
10'New Baby'349'Celebration Days > New Baby'
11'New Home'3410'Celebration Days > New Home'
13'Thank You'012'Thank You'
14'Wedding'013'Wedding'
15'Business'00'Business'
16'Valentine's Day'340'Celebration Days > Valentine's Day'
26'New Year'00'New Year'
28'Retirement'00'Retirement'
29'Sports Events/ Teams'00'Sports Events/ Teams'
34'Celebration Days'00'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_idnameparent_idsort_ordergroup_name
14'Wedding'013
15'Business'00
16'Valentine's Day'340
34'Celebration Days'00'Father's Day > Mother's Day > New Baby > New Home > Valentine's Day'

I've created an SQL Fiddle for testing.

Ad

Answer

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

Ad
source: stackoverflow.com
Ad