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.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Binary Tree Nodes
  5. Discussions

Binary Tree Nodes

Sort by

recency

|

2311 Discussions

|

  • shawejchaus
     + 0 comments

    SELECT N, IF(P IS NULL, 'Root', if( N IN (SELECT DISTINCT P FROM BST), 'Inner', 'Leaf' ) ) as bts FROM BST ORDER BY N;

  • prakulbagchi
     + 0 comments

    MS SQL

    SELECT n, CASE WHEN p IS NULL THEN 'Root' WHEN n IN (SELECT p FROM BST where p is not null) THEN 'Inner' ELSE 'Leaf' END AS type FROM BST ORDER BY n;

  • saxenaabhinav113
     + 0 comments
    SELECT
    B1.N,
    CASE
        WHEN B1.P IS NULL THEN "Root"
        WHEN NOT EXISTS (SELECT 1 FROM BST B2 WHERE B2.P = B1.N) THEN 'Leaf'
        ELSE "Inner"
    END AS NodeType
FROM 
    BST B1 
ORDER  
	BY B1.N;

    Explanation: For leaf Node - We know that leaf node is parent to none of the node. The condition will determine whether a node B1.N is not a parent to any other node. If there is no row in the table where B2.P (parent column) equals B1.N (current node), then the current node is considered a Leaf.

    SELECT 1 FROM BST B2 WHERE B2.P = B1.N looks for any child nodes where B1.N appears as a parent.

    NOT EXISTS checks if there is no match for a given condition. Example: For N=1 Go to B2 and check if - B2.P = 1 -> Does any row in the table have P = 1? No such row exists. Hence, 1 is a Leaf.

    If no match exists, the node has no children and is therefore a Leaf.

    ORDER BY B1.N;

  • ericstricker73
     + 0 comments
    select  a.n, max(
    case when b.n is null and c.n is not null then
            'Root'
         when b.n is not null and c.n is null then
            'Leaf'
         else
            'Inner'
    end )
from bst a
left join bst b on a.p = b.n
left join bst c on c.p = a.n
group by a.n
order by a.n ;

  • galinaveensai7
     + 0 comments

    tried in oracle let me know if this cna optimized further.

    select num, case when (select N from bst where p is null)=num then 'Root' when (select count(N) from bst where n=num)=1 and (select count(P) from bst where p=num)>1 then 'Inner' when (select count(N) from bst where n=num)=1 and (select count(P) from bst where p=num)=0 then 'Leaf' end from ( select N num from bst union select P num from bst) where num is not null;