'MySQL using WITH clause

I'm using MySQL to solve this problem on hackerrank.

Why this code doesn't work

WITH P_node AS (
SELECT DISTINCT P FROM BST)

SELECT CASE
    WHEN P IS NULL THEN CONCAT(N, ' Root')
    WHEN N IN P_node THEN CONCAT(N, ' Inner')
    ELSE CONCAT(N, ' Leaf') 
    END
FROM BST
ORDER BY N;

But this code works? I'm confused, can somebody help me?

SELECT CASE
    WHEN P IS NULL THEN CONCAT(N, ' Root')
    WHEN N IN (SELECT DISTINCT P FROM BST) THEN CONCAT(N, ' Inner')
    ELSE CONCAT(N, ' Leaf')
    END
FROM BST
ORDER BY N ASC
mysqlsql


Solution 1:[1]

Correct syntax is:

WITH P_node AS (
SELECT DISTINCT P FROM BST)

SELECT CASE
    WHEN P IS NULL THEN CONCAT(N, ' Root')
    WHEN N IN (select P from P_node) THEN CONCAT(N, ' Inner')
    ELSE CONCAT(N, ' Leaf') 
    END
FROM BST
ORDER BY N;

Basically replace P_node with (select P from P_node) in WHEN N IN P_node THEN CONCAT(N, ' Inner')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Zakaria

Related Questions