Recursive Query on a self referential table (not hierarchical)

I am creating a state chart of sorts with the data being stored in a simple self referencing table (JobPath)

JobId - ParentJobId

I was using a standard SQL CTE to get the data out which was working perfectly until I ended up with the following data

JobId - ParentId
  1        2
  2        3
  3        4
  4        2

Now as you can see Job 4 links to Job 2 which goes to Job 3 and th开发者_C百科en to Job 4 and so on.

Is there any way I can tell my query not to pull out data it already has?

Here is my current query

WITH JobPathTemp (JobId, ParentId, Level)
AS
(
-- Anchor member definition
    SELECT j.JobId, jp.ParentJobId, 1 AS Level
    FROM Job AS j
    LEFT OUTER JOIN dbo.JobPath AS jp
        ON j.JobId = jp.JobId
    where j.JobId=1516
    UNION ALL
-- Recursive member definition
    SELECT  j.JobId, jp.ParentJobId, Level + 1
    FROM dbo.Job as j
    INNER JOIN dbo.JobPath AS jp
        ON j.JobId = jp.JobId
    INNER JOIN JobPathTemp AS jpt
        ON jpt.ParentId = jp.JobId
      WHERE jp.ParentJobId <> jpt.JobId 
)

-- Statement that executes the CTE

SELECT * FROM JobPathTemp

If you are not dealing with a large number of entries, the following solution might be suitable. The idea is to build the complete "id path" for each row and make sure the "current id" (in the recursive part) is not already in the path being processed:

(I removed the join to jobpath for testing purposes but the basic pattern should be the same)

WITH JobPathTemp (JobId, ParentId, Level, id_path)
AS
(
  SELECT jobid, 
         parentid, 
         1 as level, 
         '|' + cast(jobid as varchar(max)) as id_path
  FROM job
  WHERE jobid = 1

  UNION ALL

  SELECT  j.JobId, 
          j.parentid, 
          Level + 1, 
          jpt.id_path + '|' + cast(j.jobid as varchar(max))
  FROM Job as j
    INNER JOIN JobPathTemp AS jpt ON j.jobid = jpt.parentid 
                                     AND charindex('|' + cast(j.jobid as varchar), jpt.id_path) = 0
)
SELECT *
FROM JobPathTemp
;

This solution doesn't work, SQL Server doesn't support using UNION to join together the recursive term. Since you can't refer to the the recursion except as the join, tbh I don't see any alternative to using a stored function...

You didn't post your query... but I tried (in postgres, which works in much the same way) and if you use "UNION" (not "UNION ALL") in the recursive term, then it should automatically remove duplicate rows:

with /*recursive*/ jobs as
 (select jobpath.jobid, jobpath.parentjobid from jobpath where jobid = 1
  union
  select jobpath.jobid, jobpath.parentjobid
  from jobpath
       join jobs on jobs.parentjobid = jobpath.jobid
 )
select jobpath.* from jobpath join jobs on jobpath.jobid = jobs.jobid;