Parent or Leaf in SQL-CodePudding

Please I have a table structured this way.

Number	Parent
1	NULL
2	1
3	2

I want to carry out a SQL Query that goes through the rows and for each row outputs one of two values for the case:

If the Number is a parent of another number, the case will output "Parent" Label for that row. e.g number 1 and 2
If the Number is not a parent i.e a leaf, the case statement will output "Not Parent" for that row. e.g Number 3. Please, how can I do this? I really am stumped about how to structure the case statement. New to SQL. Thanks

CodePudding user response：

Numbers that are parent are in the parent column.
Numbers that are leaf are not parent

select n.number,
       case 
          when p.parent is not null then ‘parent’
             else ‘leaf’
          end as number_type
  from mytable n
  left 
  join mytable p
    on n.number = p.parent;

CodePudding user response：

You can also use EXISTS

SELECT 
     Number, 
     case when exists (
           select 1 
           from mytable t2 
           where t2.Parent=t1.Number) 
     then 'Parent' else 'Leaf' end nmbrType
FROM mytable t1
ORDER BY Number

CodePudding user response：

WITH recursive cte AS (
  SELECT 
     Number, 
     Parent 
  FROM mytable 
  WHERE mytable.Number=3
  
  UNION ALL
  
  SELECT 
     mytable.Number, 
     mytable.Parent 
  FROM cte
  INNER JOIN mytable ON mytable.Number = cte.Parent
)
SELECT * FROM cte;

Start by selecting the start values (SELECT ... WHERE mytable.Number=3)
In the recusrive part select the parent (SELECT FROM cte INNER JOIN ON ....)

see: DBFIDDLE