Re: WITH RECURSIVE question

Marc Mamin <M.Mamin@intershop.de> wrote:

> WITH RECURSIVE struc (pref, id, depth ) AS (
>   SELECT '', node, 1 from forest where node= 4
>   UNION ALL
>   SELECT (case when struc.pref= '' then '\' else struc.pref end )||
> '...' ,
>          node,
>          struc.depth +1
>   FROM forest JOIN struc ON parent=struc.id
>   )
>   SELECT * FROM struc;
>
>  (path,node,depth)
>            4  1
>   \...     5  2
>   \......  6  3
>
> This is fine as long as I start with a given node (here node= 4).
>
> But How can I retrieve the complete structure in one query ?

Start with parent is null in the first query (instead of 'where
node=4').

*untested*


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°