How do I do a WHERE NOT IN for Hierarchical data?

Question

I have a table that is a list of paths between points. I want to create a query to return a list with pointID and range(number of point) from a given point. But have spent a day trying to figure this out and haven't go any where, does any one know how this should be done? ( I am writing this for MS-SQL 2005)

example

-- fromPointID | toPointID |
---------------|-----------|
--           1 |         2 |
--           2 |         1 |
--           1 |         3 |
--           3 |         1 |
--           2 |         3 |
--           3 |         2 |
--           4 |         2 |
--           2 |         4 |

with PointRanges ([fromPointID], [toPointID], [Range])
 AS
 (
    -- anchor member
     SELECT [fromPointID],
            [toPointID],
            0 AS [Range]   
     FROM dbo.[Paths]
     WHERE [toPointID] = 1
     UNION ALL
     -- recursive members
     SELECT P.[fromPointID],
            P.[toPointID],
            [Range] + 1 AS [Range]   
     FROM dbo.[Paths] AS P
     INNER JOIN PointRanges AS PR ON PR.[toPointID] = P.[fromPointID]
   WHERE [Range] < 5 -- This part is just added to limit the size of the table being returned
 --WHERE P.[fromPointID] NOT IN (SELECT [toPointID] FROM PointRanges)
 --Cant do the where statment I want to because it wont allow recurssion in the sub query
 )
 SELECT * FROM PointRanges

--Want this returned
-- PointID | Range |
-----------|-------|
--       1 |     0 |
--       2 |     1 |
--       3 |     1 |
--       4 |     2 |

I'm not sure i understood what you need, do you need all the points related to a given point and their distance to it? — fnurglewitz, Nov 18 '12 at 11:27
http://jamesmccaffrey.wordpress.com/2010/07/20/sql-graph-shortest-path-part-ii/ — Markus Jarderot, Nov 18 '12 at 12:43

score 0 · Answer 1 · answered Jul 16 '13 at 00:51

Markus Jarderot's link gives a good answer for this. I end tried using his answer and also tried rewriting my problem in C# and linq but it ended up being more of a mathematical problem than a coding problem because I had a table with several thousands of point that interlinked. This is still something I am interested in and trying to get a better understanding of by reading books on mathematics and graph theory but if anyone else runs into this problem I think Markus Jarderot's link is the best answer you will find.

How do I do a WHERE NOT IN for Hierarchical data?

1 Answers1