Solution with explanation: CASE + LEFT JOIN


  • 3
    T

    0_1499271537059_2c061b42-ed70-421a-8bd2-88f20a257d6d-image.png

    SELECT DISTINCT t1.id, (
        CASE
        WHEN t1.p_id IS NULL  THEN 'Root'
        WHEN t1.p_id IS NOT NULL AND t2.id IS NOT NULL THEN 'Inner'
        WHEN t1.p_id IS NOT NULL AND t2.id IS NULL THEN 'Leaf'
        END
    ) AS Type 
    FROM tree t1
    LEFT JOIN tree t2
    ON t1.id = t2.p_id
    

  • 0

    @tinalxj12 said in Solution with explanation: CASE + LEFT JOIN:

    SELECT DISTINCT t1.id, (
    CASE
    WHEN t1.p_id IS NULL THEN 'Root'
    WHEN t1.p_id IS NOT NULL AND t2.id IS NOT NULL THEN 'Inner'
    WHEN t1.p_id IS NOT NULL AND t2.id IS NULL THEN 'Leaf'
    END
    ) AS Type
    FROM tree t1
    LEFT JOIN tree t2
    ON t1.id = t2.p_id

    Great solution and the image is pretty clear, while I do have a suggestion

    SELECT DISTINCT t1.id, (
        CASE
        WHEN t1.p_id IS NULL  THEN 'Root'
        WHEN t2.id IS NOT NULL THEN 'Inner'
        ELSE   'Leaf'
        END
    ) AS Type 
    FROM tree t1
    LEFT JOIN tree t2
    ON t1.id = t2.p_id
    

  • 0
    L

    Great image! I think it just need t2.id is NULL to verify that is a leaf node. Is that OK?
    '''SELECT DISTINCT t1.id, (CASE WHEN t1.p_id IS NULL THEN 'Root'
    WHEN t1.p_id IS NOT NULL AND t2.id IS NOT NULL THEN 'Inner'
    WHEN t2.p_id IS NULL THEN 'Leaf'
    END) AS type
    FROM tree t1 LEFT JOIN tree t2
    ON t1.id = t2.p_id'''


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.