Jason Zhu - 7 months ago 40

SQL Question

Here's the dataset, which represents branches of a tree. Obviously, node 3, 5 are leaf nodes.

`branch_of_tree`

1

1/2

1/2/3

1/2/4

1/2/4/5

I intend to find all leaf nodes, so for the above example, it should be node 3 and node 5. Could anyone give me an idea how to solve it in PostgreSQL? Thanks!

Answer

Try this query:

```
SELECT Tb.* FROM T as Tb
WHERE NOT EXISTS (SELECT * FROM T WHERE
T.branch_of_tree LIKE Tb.branch_of_tree || '%'
AND T.branch_of_tree <> Tb.branch_of_tree )
```

Source (Stackoverflow)