Hello guys,

Do anyone know how to write recursive SQL statement for hierarhic output places from next table:

id-----place----------idParentPlace
1......World..........Null
2......Europe.........1
3......Mediterranean..2
4......North Europe...2
5......Italy..........3
6......Greece.........3
7......Island.........4
8......Norway.........4

I was looking for alot of tutorials but didn't find anything useful.

Any help is the most welcome!

Regards,
TP

Recommended Answers

You need to write HIERARCHICAL QUERY by using the following.

The START WITH...CONNECT BY clause.
The PRIOR operator.
The LEVEL pseudocolumn.

Jump to Post

All 2 Replies

Is that OK?

WITH hierarhic AS
(SELECT id, place, idParentPlace, 1 AS EmpLevel
FROM places
WHERE idParentPlace IS NULL
UNION ALL
SELECT id, place, idParentPlace,
idParentPlace + 1
FROM places
WHERE idParentPlace IS NOT NULL)
SELECT * FROM hierarhic;

You need to write HIERARCHICAL QUERY by using the following.

The START WITH...CONNECT BY clause.
The PRIOR operator.
The LEVEL pseudocolumn.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.