CODEWITHSUNDEEP
mysqlhierarchical-data
user3093048
user3093048

Reputation: 13

Getting parent/child/subchild relation in mysql

I have a single table 'tags' with the following fields (id, parent_id, name). Now I've set a limit of 3 levels in the hierarchy, i.e.: parent > child > subchild. A subchild cannot have a further child. So I want a query to retrieve records such as:

Parent-data (if parent has child) child-data (if child has subchild) subchild-data

Upvotes: 1

Views: 1397

Answers (1)

xlecoustillier
xlecoustillier

Reputation: 16351

Try something like:

SELECT tparent.id   AS parent_id, 
       tparent.name AS parent_name, 
       tchild1.id   AS child_id, 
       tchild1.name AS child_name, 
       tchild2.id   AS subchild_id, 
       tchild2.name AS subchild_name 
FROM   tags tparent 
       LEFT JOIN tags tchild1 
              ON tparent.id = tchild1.parent_id 
       LEFT JOIN tags tchild2 
              ON tchild1.id = tchild2.parent_id

According to your comment, you're looking for the following output:

ID | PARENT | NAME
 1 |      0 | family
 2 |      1 | male
 3 |      2 | boy1
 4 |      2 | boy2
 5 |      1 | female
 6 |      5 | girl1

I will assume that the ids won't always be in this order, cause if they are, problem solved :)

I'm not sure you can achieve this directly in SQL without adding some additional information that will be used for ordering. For instance, you could add another column where you'd concatenate the ids of parent-child-subchild. Something like:

-- parent
SELECT CONCAT(LPAD(id, 6, '0'), '-000000-000000') AS order_info,
       id                                         AS id,
       parent_id                                  AS parent,
       name                                       AS name
FROM   tags
WHERE  parent_id = 0
UNION
-- child
SELECT CONCAT_WS('-', LPAD(tparent.id, 6, '0'), 
                      LPAD(tchild1.id, 6, '0'),
                      '000000'),
       tchild1.id,
       tparent.id,
       tchild1.name
FROM   tags tparent
       INNER JOIN tags tchild1
               ON tparent.id = tchild1.parent_id
WHERE  tparent.parent_id = 0
UNION
-- subchild
SELECT CONCAT_WS('-', LPAD(tparent.id, 6, '0'), 
                      LPAD(tchild1.id, 6, '0'),
                      LPAD(tchild2.id, 6, '0')),
       tchild2.id,
       tchild1.id,
       tchild2.name
FROM   tags tparent
       INNER JOIN tags tchild1
               ON tparent.id = tchild1.parent_id
       INNER JOIN tags tchild2
               ON tchild1.id = tchild2.parent_id
ORDER  BY 1

See the fiddle illustrating this.

Here, I'm formatting the ids to keep ordering coherent. That implies to know the maximum length of the ids (I used a length of 6 here), which is trivial to guess from the id field type.

Upvotes: 1

Related Questions