We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
- Prepare
- SQL
- Advanced Select
- Binary Tree Nodes
- Discussions
Binary Tree Nodes
Binary Tree Nodes
Sort by
recency
|
2435 Discussions
|
Please Login in order to post a comment
MySQL
SELECT n, CASE WHEN n NOT IN (SELECT DISTINCT p FROM bst WHERE p IS NOT NULL ) THEN "Leaf" when p is NULL THEN "Root" ELSE "Inner" END AS 'node_type' FROM bst ORDER BY n ASC;
((MYSQL))
SELECT N, CASE WHEN P IS NULL THEN "Root" WHEN N IN(SELECT P FROM BST) THEN "Inner" ELSE "Leaf" END AS node FROM BST ORDER BY N;
For MySQL
SELECT N,
CASE
WHEN P IS NULL THEN 'Root'
WHEN N NOT IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf'
ELSE 'Inner'
END AS node_type
FROM BST ORDER BY N;
* WHEN N NOT IN(SELECT DISTINCT(P) FROM BST) THEN "Leaf" ELSE * We want N NOT IN(SELECT DISTINCT(P) FROM BST) to return True for nodes which are not present in attribute P In SQL, NOT IN fails if the subquery returns even one NULL. The subquery return Null for Root node. When you do N NOT IN (...) and the list contains NULL, the result is unknown, so the whole WHEN condition is false, even for correct leaves. * ....WHEN N NOT IN(SELECT DISTINCT(P) FROM BST WHERE P IS NOT NULL) THEN "Leaf" ....* for desired result NOTE :- ...WHERE P IS NOT NULL... for avoiding error due to edge cases