Vinny Vinny - 2 months ago 6
MySQL Question

MySQL Case Statement Issues

I have a table shown as below which is a representation of tree.

id --- parentid
1 NULL
2 1
3 1
4 2


Here 1 is the root node and 4,3 are leaf nodes and 2 is intermidiate node. How to write a sql query which prints

id ---- type
1 root
2 intermiditae
3 leaf
4 leaf

Answer

Here is one way of doing it:

SELECT mytable.id, IF(mytable.parent_id IS NULL, 'root',
                      IF(COUNT(children.id) > 0, 'intermediate', 'leaf'))
FROM mytable
LEFT JOIN mytable AS children ON children.parent_id = mytable.id
GROUP BY mytable.id;

The "root" is the row for which parent_id is NULL. Then, counting the number of children is enough to determine if the entry is a leaf or not.


You can also obtain the same result with 3 queries and UNION.

(SELECT mytable.id, 'root' AS type
 FROM mytable
 WHERE mytable.parent_id IS NULL)
UNION
(SELECT mytable.id, 'intermediate' AS type
 FROM mytable
 JOIN mytable AS children ON children.parent_id = mytable.id
 WHERE mytable.parent_id IS NOT NULL
 GROUP BY mytable.id)
UNION
(SELECT mytable.id, 'leaf' AS type
 FROM mytable
 LEFT JOIN mytable AS children ON children.parent_id = mytable.id
 WHERE children.id IS NULL);
Comments