0

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

Edited by Tr1umPr0

2
Contributors
1
Reply
5
Views
4 Years
Discussion Span
Last Post by Biiim
0

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

Edited by Biiim: link

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.