title: Self Referencing / CTE / Finding a trail for one mapped Entity
body: <p>Aaaack.</p>
  
  <p>I'm having a brain freeze moment.</p>
  
  <p>Below I have a self referencing table.
  And I have a CTE to self-join and get the info.</p>
  
  <p>What I'm trying to get is the "complete hierarchy path" for ONE person.</p>
  
  <p>Note that I do NOT have a predetermined number of levels.  (My example below is made up, please take the person to location relationship with a grain of salt, it ~represents my data structure, it is not my real data structure)</p>
  
  <p>Note that @PersonUUID_Me is mapped to the "Town" level, while @PersonUUID_Nobody is mapped to the street level.  Aka, the 2 different people have 2 different row-counts for their hierarchy.  So I cannot bank on "Depth" being 3 (or anything pre-determined).</p>
  
  <p>What I get now is only the "bottom" row for the Person_Me.</p>
  
  <pre><code>AAAAAAAA-6666-6666-6666-666666666666    Strasburg   AAAAAAAA-7777-7777-7777-777777777777    3
  </code></pre>
  
  <p>What I'd like to get:</p>
  
  <pre><code>99999999-9999-9999-9999-999999999999    USA (NULL)  0
  AAAAAAAA-8888-8888-8888-888888888888    Virginia    99999999-9999-9999-9999-999999999999    1
  AAAAAAAA-7777-7777-7777-777777777777    Shenandoah  AAAAAAAA-8888-8888-8888-888888888888    2
  AAAAAAAA-6666-6666-6666-666666666666    Strasburg   AAAAAAAA-7777-7777-7777-777777777777    3
  </code></pre>
  
  <p>and (not shown), if I ran it for the Person_Nobody, I would get:</p>
  
  <pre><code>99999999-9999-9999-9999-999999999999    USA (NULL)  0
  BBBBBBBB-8888-8888-8888-888888888888    North Carolina  99999999-9999-9999-9999-999999999999    1
  BBBBBBBB-7777-7777-7777-777777777777    Wake    BBBBBBBB-8888-8888-8888-888888888888    2
  BBBBBBBB-6666-6666-6666-666666666666    Raleigh BBBBBBBB-7777-7777-7777-777777777777    3
  BBBBBBBB-5555-5555-5555-555555555555    Main St BBBBBBBB-6666-6666-6666-666666666666    4
  </code></pre>
  
  <p>Gaaaa.  I'm missing something basic, I know, but I'm brain-freezing.</p>
  
  <p>Again, I'm trying to get the complete hierarchy trail for one person, the person is mapped to the "bottom-most" hierarchy-element.....but the "depth" of that element is not pre-determined.  In this example, it would be "I'll map the hierarchy value that I know the most about, but I may not know the most detailed piece of information (street), I may only know your state or town..</p>
  
  <pre><code>-- START TSQL
  
  SET NOCOUNT ON
  
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
  BEGIN 
  DROP TABLE [dbo].[Person] 
  END 
  
  
  
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BreadCrumbTrail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
  BEGIN 
  DROP TABLE [dbo].[BreadCrumbTrail] 
  END 
  GO
  
  
  
  CREATE TABLE [dbo].[BreadCrumbTrail] ( 
        [BreadCrumbTrailSurrogateUUID] [uniqueidentifier] NOT NULL
      , [BreadCrumbTrailName] varchar(64) NOT NULL
      , [CreateDate] [datetime] NOT NULL
      , ParentBreadCrumbTrailSurrogateUUID [uniqueidentifier] NULL
  
  )
  
  
  GO
  
  ALTER TABLE dbo.BreadCrumbTrail ADD CONSTRAINT PK_BreadCrumbTrail PRIMARY KEY NONCLUSTERED (BreadCrumbTrailSurrogateUUID) 
  GO
  
  ALTER TABLE [dbo].[BreadCrumbTrail] ADD CONSTRAINT FK_BreadCrumbTrail_SelfRef FOREIGN KEY ([ParentBreadCrumbTrailSurrogateUUID]) REFERENCES dbo.[BreadCrumbTrail] (BreadCrumbTrailSurrogateUUID) 
  GO
  
  
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
  BEGIN 
  DROP TABLE [dbo].[Person] 
  END 
  
  
  CREATE TABLE [dbo].[Person] ( 
  
      [PersonUUID] [uniqueidentifier] NOT NULL,
      [BreadCrumbTrailSurrogateUUID]  [uniqueidentifier] NOT NULL,
      [LastName] [varchar](64) NOT NULL,
      [FirstName] [varchar](64) NOT NULL,
      [CreateDate] [datetime] NOT NULL
      )
  
  GO
  
  ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonUUID) 
  GO
  
  ALTER TABLE [dbo].Person ADD CONSTRAINT FK_Person_To_BreadCrumb FOREIGN KEY ([BreadCrumbTrailSurrogateUUID]) REFERENCES dbo.[BreadCrumbTrail] (BreadCrumbTrailSurrogateUUID) 
  GO
  
  
  
  
  declare @PersonUUID_Me [uniqueidentifier]
  select @PersonUUID_Me = '11111111-1111-1111-1111-111111111111'
  
  declare @PersonUUID_Nobody [uniqueidentifier]
  select @PersonUUID_Nobody = '22222222-2222-2222-2222-222222222222'
  
  declare @BreadCrumbTrailSurrogateUUID_Country [uniqueidentifier]
  select @BreadCrumbTrailSurrogateUUID_Country = '99999999-9999-9999-9999-999999999999'
  declare @BreadCrumbTrailSurrogateUUID_State1 [uniqueidentifier]
  select @BreadCrumbTrailSurrogateUUID_State1 = 'AAAAAAAA-8888-8888-8888-888888888888'
  declare @BreadCrumbTrailSurrogateUUID_County1 [uniqueidentifier]
  select @BreadCrumbTrailSurrogateUUID_County1 = 'AAAAAAAA-7777-7777-7777-777777777777'
  declare @BreadCrumbTrailSurrogateUUID_Town1 [uniqueidentifier]
  select @BreadCrumbTrailSurrogateUUID_Town1 = 'AAAAAAAA-6666-6666-6666-666666666666'
  
  declare @BreadCrumbTrailSurrogateUUID_State2 [uniqueidentifier]
  select @BreadCrumbTrailSurrogateUUID_State2 = 'BBBBBBBB-8888-8888-8888-888888888888'
  declare @BreadCrumbTrailSurrogateUUID_County2 [uniqueidentifier]
  select @BreadCrumbTrailSurrogateUUID_County2 = 'BBBBBBBB-7777-7777-7777-777777777777'
  declare @BreadCrumbTrailSurrogateUUID_Town2 [uniqueidentifier]
  select @BreadCrumbTrailSurrogateUUID_Town2 = 'BBBBBBBB-6666-6666-6666-666666666666'
  
  declare @BreadCrumbTrailSurrogateUUID_Street2 [uniqueidentifier]
  select @BreadCrumbTrailSurrogateUUID_Street2 = 'BBBBBBBB-5555-5555-5555-555555555555'
  
  
  
  INSERT INTO [dbo].[BreadCrumbTrail] ( 
        [BreadCrumbTrailSurrogateUUID] 
      , [BreadCrumbTrailName]
      , [CreateDate] 
      , ParentBreadCrumbTrailSurrogateUUID
       )
  Select @BreadCrumbTrailSurrogateUUID_Country , 'USA' , CURRENT_TIMESTAMP , NULL
  UNION ALL Select @BreadCrumbTrailSurrogateUUID_State1 , 'Virginia' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_Country
  UNION ALL Select @BreadCrumbTrailSurrogateUUID_County1 , 'Shenandoah' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_State1
  UNION ALL Select @BreadCrumbTrailSurrogateUUID_Town1, 'Strasburg' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_County1
  UNION ALL Select @BreadCrumbTrailSurrogateUUID_State2 , 'North Carolina' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_Country
  UNION ALL Select @BreadCrumbTrailSurrogateUUID_County2 , 'Wake' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_State2
  UNION ALL Select @BreadCrumbTrailSurrogateUUID_Town2, 'Raleigh' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_County2
  UNION ALL Select @BreadCrumbTrailSurrogateUUID_Street2, 'Main St' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_Town2
  
  
  INSERT INTO [dbo].[Person] (    [PersonUUID] , [BreadCrumbTrailSurrogateUUID] , [LastName] , [FirstName] , [CreateDate] )
              Select @PersonUUID_Me , @BreadCrumbTrailSurrogateUUID_Town1 ,  'Coder', 'Granada' , CURRENT_TIMESTAMP
  UNION ALL   Select @PersonUUID_Nobody , @BreadCrumbTrailSurrogateUUID_Street2 ,  'Body', 'No' , CURRENT_TIMESTAMP
  
  
  
  
  /*
  Select  [PersonUUID] , [BreadCrumbTrailSurrogateUUID] , [LastName] , [FirstName] , [CreateDate] 
  from [dbo].[Person] per
      where per.PersonUUID = @PersonUUID_Me
  */
  
  
  
  
   ;WITH cteLocation (BreadCrumbTrailSurrogateUUID, [BreadCrumbTrailName] , ParentBreadCrumbTrailSurrogateUUID , Depth)
   AS
   (
      Select bc1.BreadCrumbTrailSurrogateUUID, bc1.[BreadCrumbTrailName], bc1.ParentBreadCrumbTrailSurrogateUUID , 0 as Depth From dbo.BreadCrumbTrail bc1 where bc1.ParentBreadCrumbTrailSurrogateUUID IS NULL
      Union ALL
      Select bc2.BreadCrumbTrailSurrogateUUID, bc2.[BreadCrumbTrailName] , bc2.ParentBreadCrumbTrailSurrogateUUID , Depth + 1 
      From dbo.BreadCrumbTrail bc2 
      inner join cteLocation cteL on bc2.ParentBreadCrumbTrailSurrogateUUID = cteL.BreadCrumbTrailSurrogateUUID
   )
  
   Select cteLoc.BreadCrumbTrailSurrogateUUID, [BreadCrumbTrailName] , ParentBreadCrumbTrailSurrogateUUID , Depth 
   from cteLocation cteLoc
   join [dbo].[Person] per on per.BreadCrumbTrailSurrogateUUID = cteLoc.BreadCrumbTrailSurrogateUUID
      where per.PersonUUID = @PersonUUID_Me
  </code></pre>