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
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.
http://stackoverflow.com/questions/25808341/getting-parent-child-subchild-relation-in-mysql