Selecting an heirarchial tree path for a child node

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  March 18, 2009 at 12:47 am

  #73588

  Hi,

  We have this location table - locationid, location, parentid. It is a tree like structure, that starts with North America and ends with street level child nodes. Given a child node, how can I get the path (list of locationids) from the top root to this child node? This is what I came up with, can this query be optimized? (root element's parent is itself)

  CREATE TABLE #locations (locationid INT, location VARCHAR(20), parentid INT)

  INSERT INTO #locations (locationid, location, parentid)

  SELECT 100, 'North America', 100 UNION ALL

  SELECT 101, 'North East', 100 UNION ALL

  SELECT 102, 'New York', 101 UNION ALL

  SELECT 103, 'Boston', 101 UNION ALL

  SELECT 104, 'Times Square', 102 UNION ALL

  SELECT 105, 'Wall Street', 102 UNION ALL

  SELECT 106, 'West', 100 UNION ALL

  SELECT 107, 'San Francisco', 106 UNION ALL

  SELECT 108, 'Embarcadero', 107

  --for a given locationid 105, select all its parents

  DECLARE @locationid INT

  DECLARE @parentid INT

  SELECT @locationid = 105

  SELECT @parentid = parentid FROM #locations WHERE locationid = @locationid

  --to hold the results

  CREATE TABLE #locationtree (locationid INT)

  INSERT INTO #locationtree SELECT @locationid

  WHILE @locationid <> @parentid

  BEGIN

  SELECT @locationid = locationid, @parentid = parentid FROM #locations WHERE locationid = @parentid

  INSERT INTO #locationtree SELECT @locationid

  END

  SELECT * FROM #locations

  SELECT * FROM #locationtree

  DROP TABLE #locations

  DROP TABLE #locationtree

• Mando2

  SSChasing Mays

  Points: 609

  More actions

  March 19, 2009 at 6:21 pm

  #963243

  If you haven't already looked into it, you may want to take a look at CTEs. The following may work for you - check it out:

  select *

  from #locations;

  with x(locationid, location, pareniD, path) as

  (

  select *, CONVERT (varchar(max), '') as path

  from #locations

  where locationid = parentid

  union all

  select l.*, CONVERT (varchar(max), cast(parentid as varchar) + ', ' + x.path) as path

  from #locations l

  join x on l.parentid = x.locationid

  where l.locationid <> l.parentid

  )

  select * from x