postgresql WITH RECURSIVE query to get category and subcategories
<p>I have a table as below </p>

<p><a href="https://i.stack.imgur.com/E5TWD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E5TWD.png" alt="enter image description here"></a></p>

<p>Using the WITH RECURSIVE statement i am able to get the depth of the table</p>

<pre><code>WITH RECURSIVE category_tree(id, name, depth) AS (
  SELECT id, name, ARRAY[id]
  FROM category
  WHERE parentid IS NULL
UNION ALL
  SELECT category.id, category.name, depth || category.id
  FROM category_tree
  JOIN category ON category.parentid=category_tree.id
  WHERE NOT category.id = ANY(depth)
)
SELECT * FROM category_tree ORDER BY id;
</code></pre>

<p>How should i change the query such that i can get the output as below (till the nth level of the tree)?</p>

<p><a href="https://i.stack.imgur.com/V5WFr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/V5WFr.png" alt="enter image description here"></a></p>

<p>I intend to use the above result to generate a site map like below:</p>

<p><a href="https://i.stack.imgur.com/KizMs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KizMs.png" alt="enter image description here"></a></p>

<p>I am currently trying to use the LEFT OUTER JOIN with WITH RECURSIVE statement but i am unable to find how ? Can some one please be able to guide?</p>