Question 22168 in Mysql Question posted in
The official MySQL documentation can be found here.

Parent or Leaf in SQL – Mysql

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:

  1. If the Number is a parent of another number, the case will output "Parent" Label for that row. e.g number 1 and 2
  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
Tags:

3

Answers


  1. 0
    1. Numbers that are parent are in the parent column.
    2. 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;
    Login or Signup to reply.
  2. 0 
    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

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search