Recursive Query on a self referential table (not hierarchical)

Question

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 then 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

"DISTINCT operator is not allowed in the recursive part of a recursive common table expression 'JobPathTemp'." — Gavin Mannion, Jan 07 '11 at 11:33

score 3 · Accepted Answer · answered Jan 07 '11 at 11:55

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
;

There is a limit to the amount of data there will be so this solution 'should' work. I am getting a lot of duplicates that DISTINCT isn't removing though which is odd. However my C# code can handle that without any major hassle. — Gavin Mannion, Jan 07 '11 at 12:04
Just saw my DISTINCT problem is unrelated.. this works perfectly :) — Gavin Mannion, Jan 07 '11 at 12:08

araqnid · Answer 2 · 2011-01-07T11:47:09.660

0

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;

edited Jan 07 '11 at 11:47

answered Jan 07 '11 at 11:32

araqnid

127,052
24
157
134

I added my query now, when I change to just Union I get this "Recursive common table expression 'JobPathTemp' does not contain a top-level UNION ALL operator." – Gavin Mannion Jan 07 '11 at 11:34
That's not a good sign. And you can't refer to the recursive term twice, e.g. to use a WHERE NOT EXISTS clause. – araqnid Jan 07 '11 at 11:44
No I tried that as well but it throws up an error if I refer to JobPathTemp twice (even with an alias) – Gavin Mannion Jan 07 '11 at 11:57

Recursive Query on a self referential table (not hierarchical)

2 Answers2

Linked