Recursive SQL statement

[primzon]

Hello,

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

TABLE PLACES:
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 don't know if this is totally correct SQL recursive statement:

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

Any help please?

Regards,
TP

[Biiim]

I didn't go to uni so i don't know that linguo, what is it you want?

(SELECT id, place, idParentPlace, 1 AS hierarhic
FROM places
WHERE idParentPlace IS NULL)
UNION
(SELECT id, place, idParentPlace,
idParentPlace + 1
FROM places
WHERE idParentPlace IS NOT NULL) 

Maybe this is what you are wanting: hierarchical-data-database