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

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.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.