PHP MySQL GROUP_CONCAT self join table-CodePudding

I am trying to self join a table for hierarchy purposes but I am stuck with the group_concat that does not want to cooperate well.

SELECT 
    c1.client AS parent, 
    GROUP_CONCAT(c2.client) AS child 
    FROM clients c1
    LEFT JOIN clients c2 ON 
    c1.client_id = c2.client_relation 
    ORDER BY 
    c1.client_id

I want to show the clients that don't have children (NULL), as well. I have fiddle with ifnull, but can't seem to figure it out.

The table should look something like this

Parent 1 -> null
Parent 2 -> Child 2.1, Child 2.2
Parent 3 -> null
Parent 4 -> Child 4.1
...

CodePudding user response：

I was simply missing the GROUP BY, thanks @Akina!

CodePudding user response：

SELECT 
    c1.client AS parent, 
    GROUP_CONCAT(c2.client) AS child 
FROM clients c1
LEFT JOIN clients c2 ON 
   c1.client_id = c2.client_relation 
GROUP BY 
   c1.client_id
ORDER BY 
   c1.client_id