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.

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

Select allOpen 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

Select allOpen in new window