Binary Tree Nodes

Sort by

recency

|

2347 Discussions

|

  • + 0 comments

    select distinct cld.N , case when pr.N is null then 'Leaf' when cld.P is null then 'Root' when pr.N is not null then 'Inner' end as node_type from BST cld left join BST pr on cld.N = pr.P order by cld.N

  • + 0 comments

    I complicated it a little. But how do you guys think this solution looks:

    WITH p_val AS (
        SELECT DISTINCT p FROM bst
    ),
    all_parents AS (
        SELECT LISTAGG(p, '-') WITHIN GROUP (ORDER BY p) AS all_parents
        FROM p_val
    )
    SELECT 
        bst.n,
        CASE 
            WHEN bst.p IS NULL THEN 'Root'
            WHEN CONCAT('-', CONCAT(all_parents.all_parents, '-')) LIKE '%-' || bst.n || '-%' THEN 'Inner'
            ELSE 'Leaf'
        END AS node_type
    FROM bst
    LEFT JOIN all_parents ON 1=1
    ORDER BY bst.n;
    
  • + 0 comments

    if in case column (N) dosent have all the required values.

    with tbl1 as (select distinct(N) from bst), tbl2 as (select distinct(P) from bst), tbl3 as ( Select N from tbl1 union Select P from tbl2), tbl4 as ( select * from tbl3 where N is not null)

    Select *, Case when tbl4.N in (Select * from tbl2 where P in (select N from BST where P is not null)) then "Inner" when tbl4.N in (Select N from BST where P is null)then "Root" ELSE 'Leaf' end as node_type from tbl4 order by N

  • + 0 comments
    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 NodeType
    FROM BST
    ORDER BY N;
    
  • + 0 comments

    MySQL Solution:

    SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT P FROM BST) THEN 'Inner' ELSE 'Leaf' END AS nodetype FROM BST ORDER BY N;