CODEWITHSUNDEEP
sqlsql-serverrecursive-queryhierarchyid
Yasin
Yasin

Reputation: 51

Using SQL HierarchyID to select respectively from Children to Parents

I need to write a Stored Procedure to return some data on an order from children to parents.It is somehow complicated to describe what I want to do, but let me try it: Imagine we have this Hierarchy called Categories: Parent > Child1 > Child2 > Child3, all stored using SQL HierarchyID:

Category Table
--------------
Cat_ID             | Cat_Name
..............................
/1/                | News
/1/1/              | NewsOfUSA
/1/1/1/            | NewsOfWestUSA
/1/1/1/1/          | NewsOfWashington

And we have saved News with these Categories As below:

News Table
-------------
News_ID     | FK_Cat_ID    | News_Content
.........................................
0001        | /1/          | one
0002        | /1/1/        | two
0003        | /1/1/1/      | three
0004        | /1/1/1/1/    | four1
0005        | /1/1/1/1/    | four2
0006        | /1/1/1/1/    | four3
0007        | /1/1/1/1/    | four4

And Finally I want to select for sxample Top Ten news with this condition:

If NewsOfWashington has 10 news then select it, else select from NewsOfWestUSA, else select from NewsOfUSA, else select from News, Until you reach ten

And the order to be selected is this

four4,four3,four2,four1,three,two,one

I have tried using Recursive CTE, but couldn't find a proper way of implementing it.

Upvotes: 1

Views: 362

Answers (3)

Ben Thul
Ben Thul

Reputation: 32697

Completely stealing the data script from Mitch's answer, this isn't too bad:

CREATE TABLE Categories
    (
      CatID hierarchyid not null
                        primary key ,
      Name nvarchar(255) not null
    );
CREATE TABLE News
    (
      NewsID int not null
                 primary key ,
      CatID hierarchyid not null ,
      NewsContent nvarchar(max) not null
    );

INSERT INTO Categories
VALUES ('/1/', 'News'),
  ('/1/1/', 'NewsOfUSA'),
  ('/1/1/1/', 'NewsOfIndiana'),
  ('/1/2/', 'NewsOfUK');

INSERT INTO News
VALUES (1, '/1/', 'Aliens invaded'),
  (2, '/1/1/', 'Aliens invaded the US'),
  (3, '/1/1/1/', 'Aliens invaded the midwest'),
  (4, '/1/2/', 'Aliens invaded the UK');

-- actual answer begins here
select TOP(10) News.[NewsContent]
from dbo.Categories as parent
join dbo.Categories as child
    on child.CatID.IsDescendantOf(parent.CatID) = 1
join News
    on News.CatID = parent.CatID
WHERE child.Name = 'NewsOfIndiana'
order by News.CatID.GetLevel() DESC

Essentially, I'm using the IsDescendentOf() method to get which categories the given category belongs to and then joining on the News items based on that new list of categories and finally sorting on the GetLevel() method (which returns how deep in the hierarchy a given value is).

Upvotes: 0

Slava Murygin
Slava Murygin

Reputation: 1955

Try following, it will return TOP 10 in the order you want:

SELECT TOP 10 CASE n.FK_Cat_ID
WHEN '/1/1/1/1/' THEN 0 
WHEN '/1/1/1/' THEN 1
WHEN '/1/1/' THEN 2
WHEN '/1/' THEN 3
Else 4 END,*
FROM News as n
INNER JOIN Category as c
ON n.FK_Cat_ID = c.Cat_ID
ORDER BY 1;

Upvotes: 0

Mitch
Mitch

Reputation: 22251

To identify the distance, look for all descendants and then sort by the difference in depth:

USE tempdb;

CREATE TABLE Categories (CatID hierarchyid not null primary key, Name nvarchar(255) not null);
CREATE TABLE News (NewsID int not null primary key, CatID hierarchyid not null, NewsContent nvarchar(max) not null);

INSERT INTO Categories
VALUES ('/1/', 'News'),
  ('/1/1/', 'NewsOfUSA'),
  ('/1/1/1/', 'NewsOfIndiana'),
  ('/1/2/', 'NewsOfUK');

INSERT INTO News
VALUES (1, '/1/', 'Aliens invaded'),
  (2, '/1/1/', 'Aliens invaded the US'),
  (3, '/1/1/1/', 'Aliens invaded the midwest'),
  (4, '/1/2/', 'Aliens invaded the UK');

DECLARE @VisitorLocation hierarchyid = '/1/1/1/';

WITH 
relevantCategories AS (
    SELECT c.*, ABS(@VisitorLocation.GetLevel() - c.CatID.GetLevel()) as RelevanceDistance
    FROM Categories c
    WHERE @VisitorLocation.IsDescendantOf(c.CatID) = 1
)
SELECT TOP(10) n.*, c.RelevanceDistance
FROM relevantCategories c
INNER JOIN News n on n.CatID = c.CatID
ORDER BY RelevanceDistance ASC, n.NewsID DESC;

DROP TABLE Categories;
DROP TABLE News;

Produces: 

  NewsID   CatID           NewsContent           RelevanceDistance  
 -------- -------- ---------------------------- ------------------- 
       3   0x5AD6   Aliens invaded the midwest                   0  
       2   0x5AC0   Aliens invaded the US                        1  
       1   0x58     Aliens invaded                               2

Upvotes: 1

Related Questions