CREATE TABLE tree (
  `id` INTEGER,
  `p_id` VARCHAR(4)
);

INSERT INTO tree
  (`id`, `p_id`)
VALUES
  ('1', null),
  ('2', '1'),
  ('3', '1'),
  ('4', '2'),
  ('5', '2');

CREATE TABLE Result (
  `id` INTEGER,
  `Type` VARCHAR(5)
);

INSERT INTO Result
  (`id`, `Type`)
VALUES
  ('1', 'Root'),
  ('2', 'Inner'),
  ('3', 'Leaf'),
  ('4', 'Leaf'),
  ('5', 'Leaf');

SELECT
    id
    ,CASE
        WHEN p_id is NULL THEN 'Root'
        WHEN id IN (
                    SELECT p_id 
                    FROM tree) 
        THEN 'INNER'
        WHEN id NOT IN(
                       SELECT p_id
                       FROM tree)
        THEN 'LEAF'
        END AS Type
FROM tree

Return

id  Type                                                                                            
1   Root                                                                                                             
2   INNER                                                                                                    
3   null                                                                                          
4   null                                                                                          
5   null   

Prefere Return

1   Root                                                                                                             
2   INNER                                                                                                    
3   LEAF                                                                                          
4   LEAF                                                                                          
5   LEAF  

and also can i get any suggestion which function can be use with case function in MySQL Function

CodePudding user response：

here is how you can do it :

select distinct t.*,case when t.p_id is null then 'root' 
                         when p.p_id is null then 'leaf' 
                         else 'inner' 
                    end as type
from tree t 
left join tree p on p.p_id = t.id 

db<>fiddle here

CodePudding user response：

Change the NOT IN subquery to filter out p_id = NULL, because when you compare with that you get NULL.

SELECT
    id
    ,CASE
        WHEN p_id is NULL THEN 'Root'
        WHEN id IN (
                    SELECT p_id 
                    FROM tree) 
        THEN 'INNER'
        WHEN id NOT IN (
                    SELECT p_id 
                    FROM tree
                    WHERE p_id IS NOT NULL) 
        THEN 'LEAF'
        END AS Type
FROM tree

DEMO

However, I actually recommend you use the LEFT JOIN method in @eshirvana's answer.