StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The answer is to use a recusive "Common Table Expression", or CTE. This allows you to build the structure of your hierarchy. Below is an example, modified to match your structure, based on this page: <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms186243.aspx</a></p> <pre><code>WITH CategoryStructured (ParentCategoryID, CategoryID, Description, Status, Level) AS ( -- Anchor member definition SELECT c.ParentCategoryID, c.CategoryID, c.Description, c.Status, 0 AS Level FROM Category AS c WHERE c.ParentCategoryID=0 UNION ALL -- Recursive member definition SELECT c.ParentCategoryID, c.CategoryID, c.Description, c.Status, Level + 1 FROM Category AS c INNER JOIN CategoryStructured AS c_parent ON c.ParentCategoryID = c_parent.CategoryID ) -- Statement that executes the CTE SELECT distinct cs.ParentCategoryID, cs.CategoryID, cs.Description, cs.Status, cs.Level FROM CategoryStructured cs, (SELECT level,ParentCategoryID,CategoryID from CategoryStructured WHERE (categoryID = 4) OR (level = 1 AND parentCategoryID = 4)) as thisCategory WHERE cs.level BETWEEN thisCategory.level - 1 AND thisCategory.level+1 AND ((thisCategory.level != 0 AND cs.ParentCategoryID = thisCategory.ParentCategoryID) OR cs.categoryID = thisCategory.ParentCategoryID OR cs.ParentCategoryID = thisCategory.CategoryID OR cs.CategoryID = thisCategory.CategoryID) </code></pre> <p>Updated to reflect your updated question.</p> <p><em>edit</em> I know you were able to get the above basically working for you with the added distinct, but I thought of a better way to handle this after I left chat:</p> <pre><code>WITH CategoryStructured (ParentCategoryID, CategoryID, Description, Status, Level) AS ( -- Anchor member definition SELECT c.ParentCategoryID, c.CategoryID, c.Description, c.Status, 0 AS Level FROM Categories AS c WHERE (c.ParentCategoryID IS NULL AND c.categoryID = 7) -- when 7 is a top level category, then it is the root level OR (c.categoryID = (SELECT c2.parentCategoryID FROM Categories c2 WHERE c2.categoryID = 7)) -- when 7 is some non-top level category, then 7's parent is the root UNION ALL -- Recursive member definition SELECT c.ParentCategoryID, c.CategoryID, c.Description, c.Status, Level + 1 FROM Categories AS c INNER JOIN CategoryStructured AS c_parent ON c.ParentCategoryID = c_parent.CategoryID ) -- Statement that executes the CTE SELECT cs.ParentCategoryID, cs.CategoryID, cs.Description, cs.Status, cs.Level FROM CategoryStructured cs WHERE cs.level < 3 ORDER BY cs.level </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload