## DEV Community π©βπ»π¨βπ» is a community of 966,155 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Posted on

# This SQL solution is GENIUS!!π€©

I recently started off solving SQL problems on Leetcode and I come across atleast one problem every day whose solutions I think are pure genius.

It's one such problem I came across today which I'll discuss below,

## Databases (608. Tree Node)

Problem statement
We need to find the type of each node in a tree. Whether it's a root, inner or a leaf node.

``````Input:
Tree table:
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
Output:
+----+-------+
| id | type  |
+----+-------+
| 1  | Root  |
| 2  | Inner |
| 3  | Leaf  |
| 4  | Leaf  |
| 5  | Leaf  |
+----+-------+
``````

`Checkout the description thoroughly from the link above and try get an idea of solving it before checking out below approaches`

## 1. My approach

I solved it using `CASE`.
I don't want to go into nitty gritty details of it but here's an overview,
1.1 If p_id is null, then it's a Root node.
1.2 If id is in p_id, then it's an Inner node.
1.3 Else, it's a Leaf node.

## 2. Genius approach (Atleast I think)

Using `If` function

Here's the code,

``````SELECT
atree.id,
IF(ISNULL(atree.p_id),
'Root',
IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')) Type
FROM
tree atree
ORDER BY atree.id
``````

Let's break it down,

1. Sets `atree` as an Alias to our Tree table.
2. Selects `id` from the Tree table.

Here's what's interesting!!!!
3.Creates an If function

``````General usage of If function π

`If(10>27, YES, NO)`
It states that if condition `10>27` is true then **YES** else **NO**.
``````

Now,
`IF(ISNULL(atree.p_id),'Root'`
states that,
if `p_id` is null, then set it as Root.
(And just like that our condition from 1.1 was satisfied)
4.Yet another If function embedded inside the `first If function as an Else condition`,
`IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')`
This states that,
If `id` is in `p_id` set it as 'Inner' else as 'Leaf'.

(And just like that our conditions 1.2, 1.3 were satsifiedπ€©)

The genius behind this solution is designing it by utilizing of simple functions ordered in a perfect way.
As complex as it may seem, as simple it is I believe.

If you made it till here, let me know what you think of this and also mention down in the comments if there are any more problems/solutions you find interesting.