We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Binary Tree Nodes
  5. Discussions

Binary Tree Nodes

  • defiant_dj04
     + 5 comments

    Solution 1: Oracle/MySQL/MS SQL Server

    SELECT n,
	CASE
		WHEN p IS NULL THEN 'Root'
		WHEN n IN (SELECT DISTINCT p FROM bst) THEN 'Inner'
		ELSE 'Leaf'
	END
FROM bst
ORDER BY n;

    Solution 2: Oracle/MySQL/MS SQL Server

    SELECT n,
	CASE
		WHEN p IS NULL THEN 'Root'
		WHEN EXISTS (SELECT * FROM bst in_bst WHERE in_bst.p = out_bst.n) THEN 'Inner'
		ELSE 'Leaf'
	END
FROM bst out_bst
ORDER BY n;

    Solution 3: Oracle/MySQL/MS SQL Server

    SELECT n,
	CASE
		WHEN p IS NULL THEN 'Root'
		WHEN ((SELECT COUNT(*) FROM bst in_bst WHERE in_bst.p = out_bst.n) > 0) THEN 'Inner'
		ELSE 'Leaf'
	END
FROM bst out_bst
ORDER BY n;

    Solution 4: Oracle/MySQL/MS SQL Server

    SELECT n,
	CASE
		WHEN p IS NULL THEN 'Root'
		WHEN n IN (SELECT bst_1.p FROM bst bst_1 JOIN bst bst_2 ON bst_1.p = bst_2.n) THEN 'Inner'
		ELSE 'Leaf'
	END
FROM bst
ORDER BY n;

    Solution 5: MySQL

    SELECT n, IF(p IS NULL, 'Root', IF((SELECT COUNT(*) FROM bst in_bst WHERE in_bst.p = out_bst.n) > 0, 'Inner', 'Leaf')) 
FROM bst out_bst
ORDER BY n;