You need to create a view ( View ) to a table with a tree structure ( id , parent_id ), referring to which you can get:

  • all items

  • all child elements of the required element (pass id )

  • all parent elements of the desired element to the highest level (pass id )

Not to offer hierarchyid , MS SQL Server 2005 does not support it. :(

Use the stored procedure not to offer (it should be a view that I could access from the filter with the IN operator or wait for it without creating a temporary table)

These restrictions, I do not impose harm. Just the architecture of the project is very specific.

  • msdn.microsoft.com/ru-ru/library/ms175972 ( v=sql.90).aspx - Pavel Mayorov
  • And it fits.stackoverflow.com/questions/503409/… - Mike
  • one
    Usually this is not done through views, but through table functions with a parameter. - i-one
  • @PavelMayorov, I can google ... and not even a noob in queries. But I can't come up with a view that meets the requirements - iRumba
  • one
    @iRumba And why this should be a view. You do not transfer parameter. and the starting id is just a parameter, so as I indicated above using CTE and this is the only way - Mike

1 answer 1

If there is a table-tree, for example:

 CREATE TABLE tree (ID int not NULL, ParentID int NULL, PRIMARY KEY (ID)); INSERT INTO tree (ID, ParentID) VALUES (1, NULL), (2, 1), (4, 2), (5, 2), (6, 2), (3, 1), (7, 3), (8, 3), (9, 3), (10, 3);

and when working with it there are many places that require a recursive code, then for convenience I recommend having two table functions.

Something like

 CREATE FUNCTION treeItemAncestors ( @itemID int ) RETURNS TABLE AS RETURN WITH cte(ID, ParentID) AS ( SELECT ID, ParentID FROM tree WHERE ID = @itemID UNION ALL SELECT t.ID, t.ParentID FROM tree t JOIN cte ON cte.ParentID = t.ID ) SELECT ID FROM cte GO

and

 CREATE FUNCTION treeItemDescendants ( @itemID int ) RETURNS TABLE AS RETURN WITH cte(ParentID, ID) AS ( SELECT ParentID, ID FROM tree WHERE ID = @itemID UNION ALL SELECT t.ParentID, t.ID FROM tree t JOIN cte ON cte.ID = t.ParentID ) SELECT ID FROM cte GO

I called them descendants and ancestors , but they also return the node itself, which is passed as a parameter (based on my practice, most often this is what is required, but if necessary, you can exclude it). Also, they only return an ID list from me. If you need other columns, then you need to add them to the cte and the resulting select .

In order for treeItemAncestors to treeItemAncestors well in this case, the existing clustered primary key is sufficient.

For good work treeItemDescendants it is desirable to add an index on the ParentID :

 CREATE INDEX ix_tree_ParentID ON tree (ParentID) INCLUDE (ID);

Other fields, if any, are necessary, can also be added to include .

Then you can work with them, for example:

 SELECT ID FROM treeItemAncestors(10);

or

 SELECT DISTINCT a.ID FROM (VALUES (6), (9)) idlist(id) CROSS APPLY treeItemAncestors(idlist.id) a;

or

 DELETE t FROM tree t join treeItemDescendants(3) d on d.ID = t.ID;

etc.

  • For i-one How to translate a query from your example SELECT DISTINCT a. ID FROM (VALUES (6), (9)) idlist (id) CROSS APPLY treeItemAncestors (idlist.id) a; applied to MS SQL 2005 / Thanks in advance to Yuri - Juri Shutenko
  • @JuriShutenko, SqlServer 2005 was not yet VALUES, so instead of (VALUES (6), (9)) idlist(id) you need to use (SELECT 6 UNION ALL SELECT 9) idlist(id) . - i-one