mssql2012 - hierarchy traversal does not bring back the expected result set.

When I try to look for descendent organizations, I am seeing the level I am descending from and the descendents (screen shot).  I did not expect to see the row hightlighted.
Problem-.JPG
When I try to find the ancestor on level 1 for OrgId 3, I don't receive any rows. I expected to see only the highlighted row. I have either generated the data incorrectly, or I am thinking about hierarchies in a wrong way.

Here is the sql for the queries

-- query#1 (descendents)

  declare @OrgIdofInterest hierarchyid

  select @OrgIdofInterest = OrgNode
   from [stakeholder].OrganizationInheritance
   where OrgId =2;


 SELECT 
 	 orgtree.OrgNode.ToString() AS Text_OrgNode, 
     orgtree.OrgNode, 
	 orgtree.OrgLevel,
	 orgtree.OrgId 
     FROM stakeholder.OrganizationInheritance orgtree
    where OrgNode.IsDescendantOf(@OrgIdofInterest) = 1


-- query #2 (ancestor[s])

  declare @NewOrgIdofInterest hierarchyid

  select @NewOrgIdofInterest = OrgNode
   from [stakeholder].OrganizationInheritance
   where OrgId = 3;


 SELECT 
 	 orgtree.OrgNode.ToString() AS Text_OrgNode, 
     orgtree.OrgNode, 
	 orgtree.OrgLevel,
	 orgtree.OrgId 
     FROM stakeholder.OrganizationInheritance orgtree
     where OrgNode.GetAncestor(1) = @NewOrgIdofInterest

Open in new window


Here is the build SQL for the tables and stored proc.

use [scratchdb]
go


ALTER TABLE stakeholder.OrganizationInheritance
	DROP CONSTRAINT FK_OrganizationInheritance_OrganizationType
GO

ALTER TABLE stakeholder.OrganizationInheritance
	DROP CONSTRAINT FK_OrganizationInheritance_Organization
GO

ALTER TABLE stakeholder.Organization
	DROP CONSTRAINT FK_Organization_OrganizationType
GO

ALTER TABLE stakeholder.OrganizationInheritance
	DROP CONSTRAINT UQ__Organiza__420C9E6DA211482A
GO

ALTER TABLE stakeholder.OrganizationInheritance
	DROP CONSTRAINT PK_OrganizationInheritance
GO

ALTER TABLE stakeholder.Organization
	DROP CONSTRAINT PK_Organization
GO

DROP INDEX stakeholder.OrganizationInheritance.OrganizationNode
GO

DROP TABLE stakeholder.OrganizationInheritance
GO

DROP TABLE stakeholder.Organization
GO


CREATE TABLE stakeholder.Organization ( 
	OrgId           	int IDENTITY NOT NULL,
	LegacyOrgCode   	nvarchar(50) NULL,
	CustomIdentifier	nvarchar(50) NULL,
	OrgTypeId       	int NULL,
	OrgShortName    	nvarchar(50) NULL,
	OrgName         	nvarchar(200) NULL,
	OrgURL          	nvarchar(300) NULL,
	associationYears	smallint NULL 
	)
GO

CREATE TABLE stakeholder.OrganizationInheritance ( 
	OrgNode  	hierarchyid NOT NULL,
	OrgLevel 	smallint NULL,
	OrgId    	int NOT NULL,
	OrgName  	varchar(200) NOT NULL,
	OrgTypeId	int NOT NULL 
	)
GO


SET IDENTITY_INSERT stakeholder.Organization ON
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(-1, N'-1', N'', 1, N'All Fleets', N'ALL FLEETS', NULL, NULL)
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(1, N'506', NULL, 1, N'S', N'S-0', NULL, NULL)
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(2, N'626', NULL, 1, N'JJ Inc.', N'JJ-0', NULL, NULL)
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(3, N'69066', NULL, 1, N'JJ-Northeast', N'JJ-1-A', NULL, NULL)
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(4, N'70809', NULL, 1, N'JJ-Southeast', N'JJ-1-B', NULL, NULL)
GO
SET IDENTITY_INSERT stakeholder.Organization OFF
GO

CREATE PROCEDURE [stakeholder].[AddOrganizationInheritance]
(  
	 @topOrgId int,
	 @Orgid int, 
	 @OrgName varchar(100), 
	 @OrgTypeId int
	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   DECLARE @mOrgNode hierarchyid, @lc hierarchyid

   SELECT @mOrgNode = OrgNode 
    FROM  UnifiedStakeholder.stakeholder.OrganizationInheritance
   WHERE OrgId = @topOrgId

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode) 
      FROM UnifiedStakeholder.stakeholder.OrganizationInheritance
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT UnifiedStakeholder.stakeholder.OrganizationInheritance 
	  (OrgNode, OrgId, OrgName, OrgTypeId)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @OrgId, @OrgName, @OrgTypeId)

   COMMIT
	
END

GO

 	 insert UnifiedStakeholder.stakeholder.OrganizationInheritance
	 (OrgNode, OrgId,OrgName,OrgTypeId)
	 values (hierarchyid::GetRoot(),-1,'ALL FLEETS',1)
GO
	 
	 
	 insert UnifiedStakeholder.stakeholder.OrganizationInheritance
	 (OrgNode, OrgId,OrgName,OrgTypeId)
	 values (@Fleet.GetDescendant(NULL,NULL),1,'S',1)
GO

-- // Note: I could have this wrong. I think I'm putting in the level above the level I want as @topOrgId.
exec unifiedstakeholder.stakeholder.AddOrganizationInheritance -1,2,'JJ Inc.',1
GO

exec unifiedstakeholder.stakeholder.AddOrganizationInheritance 2,3,'JJ-Northeast',1
GO

exec unifiedstakeholder.stakeholder.AddOrganizationInheritance 2,4,'JJ-Southeast',1
GO

CREATE UNIQUE INDEX OrganizationNode
	ON stakeholder.OrganizationInheritance(OrgNode, OrgLevel)
	WITH FILLFACTOR = 95
GO

ALTER TABLE stakeholder.Organization
	ADD CONSTRAINT PK_Organization
	PRIMARY KEY (OrgId)
GO

ALTER TABLE stakeholder.OrganizationInheritance
	ADD CONSTRAINT PK_OrganizationInheritance
	PRIMARY KEY (OrgNode)
GO

ALTER TABLE stakeholder.OrganizationInheritance
	ADD CONSTRAINT UQ__Organiza__420C9E6DA211482A
	UNIQUE (OrgId)
GO

ALTER TABLE stakeholder.Organization
	ADD CONSTRAINT FK_Organization_OrganizationType
	FOREIGN KEY(OrgTypeId)
	REFERENCES reference.OrganizationType(OrgTypeId)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO

ALTER TABLE stakeholder.OrganizationInheritance
	ADD CONSTRAINT FK_OrganizationInheritance_OrganizationType
	FOREIGN KEY(OrgTypeId)
	REFERENCES reference.OrganizationType(OrgTypeId)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO

ALTER TABLE stakeholder.OrganizationInheritance
	ADD CONSTRAINT FK_OrganizationInheritance_Organization
	FOREIGN KEY(OrgId)
	REFERENCES stakeholder.Organization(OrgId)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Your sample is good, but @Fleet is not defined, nor used. This is your sample, after I made it run:

USE tempdb;
GO

CREATE TABLE Organization
    (
      OrgId INT IDENTITY
                NOT NULL ,
      LegacyOrgCode NVARCHAR(50) NULL ,
      CustomIdentifier NVARCHAR(50) NULL ,
      OrgTypeId INT NULL ,
      OrgShortName NVARCHAR(50) NULL ,
      OrgName NVARCHAR(200) NULL ,
      OrgURL NVARCHAR(300) NULL ,
      associationYears SMALLINT NULL
    );

CREATE TABLE OrganizationInheritance
    (
      OrgNode HIERARCHYID NOT NULL ,
      OrgLevel SMALLINT NULL ,
      OrgId INT NOT NULL ,
      OrgName VARCHAR(200) NOT NULL ,
      OrgTypeId INT NOT NULL
    );
GO

CREATE PROCEDURE AddOrganizationInheritance
    (
      @topOrgId INT ,
      @Orgid INT ,
      @OrgName VARCHAR(100) ,
      @OrgTypeId INT
	
    )
AS
    SET NOCOUNT ON;

    DECLARE @mOrgNode HIERARCHYID ,
        @lc HIERARCHYID;

    SELECT  @mOrgNode = OrgNode
    FROM    OrganizationInheritance
    WHERE   OrgId = @topOrgId;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

    SELECT  @lc = MAX(OrgNode)
    FROM    OrganizationInheritance
    WHERE   OrgNode.GetAncestor(1) = @mOrgNode;

    INSERT  OrganizationInheritance
            ( OrgNode ,
              OrgId ,
              OrgName ,
              OrgTypeId
            )
    VALUES  ( @mOrgNode.GetDescendant(@lc, NULL) ,
              @Orgid ,
              @OrgName ,
              @OrgTypeId
            );

    COMMIT TRANSACTION;	
GO

SET IDENTITY_INSERT Organization ON;

INSERT  INTO Organization
        ( OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears )
VALUES  ( -1, N'-1', N'', 1, N'All Fleets', N'ALL FLEETS', NULL, NULL ),
        ( 1, N'506', NULL, 1, N'S', N'S-0', NULL, NULL ),
        ( 2, N'626', NULL, 1, N'JJ Inc.', N'JJ-0', NULL, NULL ),
        ( 3, N'69066', NULL, 1, N'JJ-Northeast', N'JJ-1-A', NULL, NULL ),
        ( 4, N'70809', NULL, 1, N'JJ-Southeast', N'JJ-1-B', NULL, NULL );

SET IDENTITY_INSERT Organization OFF;
GO

INSERT  OrganizationInheritance
        ( OrgNode, OrgId, OrgName, OrgTypeId )
VALUES  ( hierarchyid::GetRoot(), -1, 'ALL FLEETS', 1 );
        --( @Fleet.GetDescendant(NULL, NULL), 1, 'S', 1 );
GO

SELECT  *
FROM    Organization O;

SELECT  *
FROM    OrganizationInheritance OI;
GO

-- // Note: I could have this wrong. I think I'm putting in the level above the level I want as @topOrgId.
--EXECUTE AddOrganizationInheritance -1, 2, 'JJ Inc.', 1;
--EXECUTE AddOrganizationInheritance 2, 3, 'JJ-Northeast', 1;
--EXECUTE AddOrganizationInheritance 2, 4, 'JJ-Southeast', 1;
--GO

-- query#1 (descendents)
DECLARE @OrgIdofInterest HIERARCHYID;

SELECT  @OrgIdofInterest = OrgNode
FROM    OrganizationInheritance
WHERE   OrgId = 2;

SELECT  orgtree.OrgNode.ToString() AS Text_OrgNode ,
        orgtree.OrgNode ,
        orgtree.OrgLevel ,
        orgtree.OrgId
FROM    OrganizationInheritance orgtree
WHERE   OrgNode.IsDescendantOf(@OrgIdofInterest) = 1;


-- query #2 (ancestor[s])
DECLARE @NewOrgIdofInterest HIERARCHYID;

SELECT  @NewOrgIdofInterest = OrgNode
FROM    OrganizationInheritance
WHERE   OrgId = 3;


SELECT  orgtree.OrgNode.ToString() AS Text_OrgNode ,
        orgtree.OrgNode ,
        orgtree.OrgLevel ,
        orgtree.OrgId
FROM    OrganizationInheritance orgtree
WHERE   OrgNode.GetAncestor(1) = @NewOrgIdofInterest;
GO
     
DROP PROCEDURE AddOrganizationInheritance;
DROP TABLE Organization;
DROP TABLE OrganizationInheritance;
GO

Open in new window


Can you please explain what you're trying to do here?
0
Paula DiTalloIntegration developerAuthor Commented:
Ste5an,

Sure. In a nut shell, I want to know why the levels don't work as I think they should.  I think that in the 1st example, I should only get back 2 rows--the children of JJ Inc.  Instead, I am getting back all 3 rows. In the second example, while solving for the Northeast's division, I am expecting to see only a single row for JJ Inc.  Instead, I'm getting back nothing.
0
Paula DiTalloIntegration developerAuthor Commented:
Anyone able to answer this question?
0
ste5anSenior DeveloperCommented:
Query 1: See IsDescendantOf()

Remarks

Returns true for all the nodes in the sub-tree rooted at parent, and false for all other nodes.

Parent is considered its own descendant.

E.g.
USE tempdb;
GO

DECLARE @OrganizationInheritance TABLE
    (
      OrgNode HIERARCHYID ,
      OrgId INT ,
      OrgName VARCHAR(200)
    );

INSERT  INTO @OrganizationInheritance
VALUES  ( '/1/', -1, N'All Fleets' ),
        ( '/1/1/', 1, N'S' ),
        ( '/1/1/1/', 2, N'JJ Inc.' ),
        ( '/1/1/1/1/', 3, N'JJ-Northeast' ),
        ( '/1/1/1/2/', 4, N'JJ-Southeast' );

SELECT  *
FROM    @OrganizationInheritance;

-- query#1 (descendents)
DECLARE @OrgIdofInterest HIERARCHYID;

SELECT  @OrgIdofInterest = OrgNode
FROM    @OrganizationInheritance
WHERE   OrgId = 2;

SELECT  OrgNode.ToString() AS Text_OrgNode ,
        OrgNode.GetLevel() AS Level ,
        *
FROM    @OrganizationInheritance
WHERE   OrgNode.IsDescendantOf(@OrgIdofInterest) = 1
        AND OrgNode != @OrgIdofInterest;


-- query #2 (ancestor[s])
SELECT  @OrgIdofInterest = OrgNode
FROM    @OrganizationInheritance
WHERE   OrgId = 2;

SELECT  OrgNode.ToString() AS Text_OrgNode ,
        OrgNode.GetLevel() AS Level ,
        *
FROM    @OrganizationInheritance
WHERE   OrgNode = @OrgIdofInterest.GetAncestor(1);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.