LeetCode 608. Tree Node SQL Solution

Problem

LeetCode SQL Problem

608. Tree Node

tree table

id	p_id
1
2	1
3	1
4	2
5	2

Solution - Self Join + Union

MySQL

Print the node id and the type of the node

-- Root node's parent node is NULL
SELECT id
	,'Root' AS Type
FROM tree
WHERE p_id IS NULL

UNION

-- Inner node has a parent node and a child node
SELECT N.id
	,'Inner'
FROM tree AS N
INNER JOIN tree AS P ON N.p_id = P.id
INNER JOIN tree AS C ON N.id = C.p_id

UNION

-- Leaf node has parent node, but no child node
SELECT N.id
	,'Leaf'
FROM tree AS N
INNER JOIN tree AS P ON N.p_id = P.id
LEFT JOIN tree AS C ON N.id = C.p_id
WHERE C.p_id IS NULL
ORDER BY id ASC -- Sort your output by the node id.

TSQL

Print the node id and the type of the node

-- Root node's parent node is NULL
SELECT id
	,'Root' AS Type
FROM tree
WHERE p_id IS NULL

UNION

-- Inner node has a parent node and a child node
SELECT N.id
	,'Inner'
FROM tree AS N
INNER JOIN tree AS P ON N.p_id = P.id
INNER JOIN tree AS C ON N.id = C.p_id

UNION

-- Leaf node has parent node, but no child node
SELECT N.id
	,'Leaf'
FROM tree AS N
INNER JOIN tree AS P ON N.p_id = P.id
LEFT JOIN tree AS C ON N.id = C.p_id
WHERE C.p_id IS NULL
ORDER BY id ASC -- Sort your output by the node id.

Query Output

id	Type
1	Root
2	Inner
3	Leaf
4	Leaf
5	Leaf