Hierarchical data - Getting parentchildsubchild relation in mysql

De openkb
Aller à : Navigation, rechercher

Sommaire

Questions

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

Answers

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

http://sqlfiddle.com/#!2/6e2c0/10 http://sqlfiddle.com/#!2/6e2c0/10

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.

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/25808341/getting-parent-child-subchild-relation-in-mysql

Related

Outils personnels
Espaces de noms

Variantes
Actions
Navigation
Outils