SQL Server 2005 Recursive Query with Loops in Data - Is It Possible

Loop through without Cursor in SQL Server 2005

You can use SQL 2005 CTEs to make the SQL engine do it recursively.

An enumeration of basic approaches is at http://blogs.msdn.com/anthonybloesch/archive/2006/02/15/Hierarchies-in-SQL-Server-2005.aspx

Celko also has a trees in SQL book which covers all of this to the nth degree.

Or you can brute force it by selecting each level into a local table variable and then looping, inserting children with a select, until your @@ROWCOUNT is zero (i.e., you're not finding any more children). If you don't have a lot of data, this is easy to code, but you hinted that you're looking for performance by saying you dont want a cursor.

Is it possible to create a recursive query in Access?

No, It isn't. Recursive queries are supported in SQL Server after SServer 2005, but not in Access.

If you know the number of levels beforehand, you could write a query, but it wouldn't be a recursive one.

In SQL Server, CTE (An SQL extension) is used for that : see http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx

Regular SQL however does not have Recursivity support.

Recursively grab all data based on a parent id

This can be done in SQL Server 2005 and above using Common Table Expressions (CTEs). Here is a great link from MSDN describing recursive queries: Recursive Queries Using Common Table Expressions

Here is an example:

If you imagine a hierarchical line of people, this query will let you see the complete line of any person AND calculates their place in the hierarchy. It can be modified to find any child relationship.

Instead of the ID of the person, you swap in the ID of the row you are using as your parent.

--Create table of dummy data
create table #person (
personID integer IDENTITY(1,1) NOT NULL,
name      varchar(255) not null,
dob       date,
father    integer
);

INSERT INTO #person(name,dob,father)Values('Pops','1900/1/1',NULL);  
INSERT INTO #person(name,dob,father)Values('Grandma','1903/2/4',null);
INSERT INTO #person(name,dob,father)Values('Dad','1925/4/2',1);
INSERT INTO #person(name,dob,father)Values('Uncle Kev','1927/3/3',1);
INSERT INTO #person(name,dob,father)Values('Cuz Dave','1953/7/8',4);
INSERT INTO #person(name,dob,father)Values('Billy','1954/8/1',3);

DECLARE @OldestPerson INT; 
SET @OldestPerson = 1; -- Set this value to the ID of the oldest person in the family

WITH PersonHierarchy (personID,Name,dob,father, HierarchyLevel) AS
(
   SELECT
      personID
      ,Name
      ,dob
      ,father,
      1 as HierarchyLevel
   FROM #person
   WHERE personID = @OldestPerson

   UNION ALL

   SELECT
    e.personID,
      e.Name,
      e.dob,
      e.father,
      eh.HierarchyLevel + 1 AS HierarchyLevel
   FROM #person e
      INNER JOIN PersonHierarchy eh ON
         e.father = eh.personID
)

SELECT *
FROM PersonHierarchy
ORDER BY HierarchyLevel, father;

DROP TABLE #person;

Is there a way to select Parent IDs in SQL without recursion or looping?

The operation is inherently looped. Because each node does not have any finite relation to their root, you must traverse in order to discover it.

If, for example, you knew that there was a maximum depth of N then you could create N LEFT OUTER JOINs in a single statement and display the last non-null parent ID returned this way.

The looping requirement is that you simply don't know what N is, and you cannot ask a declarative language like SQL to "figure it out"

Even if you can accomplish it with some built-in method, it will still be a loop or recursion, just obfuscated from you.

Prevent recursive CTE visiting nodes multiple times

The CTE's are recursive.

When your CTE's have multiple initial conditions, that means they also have different recursion stacks, and there is no way to use information from one stack in another stack.

In your example, the recursion stacks will go as follows:

(1) - first IN condition
(1, 2)
(1, 2, 3)
(1, 2, 3, 4)
(1, 2, 3) - no more children
(1, 2) - no more children
(1) - no more children, going to second IN condition

(3) - second condition
(3, 4)
(3) - no more children, returning

As you can see, these recursion stack do not intersect.

You could probably record the visited values in a temporary table, JOIN each value with the temptable and do not follow this value it if it's found, but SQL Server does not support these things.

So you just use SELECT DISTINCT.

Is there a way to loop through a table variable in TSQL without using a cursor?

First of all you should be absolutely sure you need to iterate through each row — set based operations will perform faster in every case I can think of and will normally use simpler code.

Depending on your data it may be possible to loop using just SELECT statements as shown below:

Declare @Id int

While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
    Select Top 1 @Id = Id From ATable Where Processed = 0

    --Do some processing here

    Update ATable Set Processed = 1 Where Id = @Id 

End

Another alternative is to use a temporary table:

Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

The option you should choose really depends on the structure and volume of your data.

Note: If you are using SQL Server you would be better served using:

WHILE EXISTS(SELECT * FROM #Temp)

Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).

Is it possible to make a recursive SQL query?

There are a few ways to do what you need in PostgreSQL.

• If you can install modules, look at the tablefunc contrib. It has a connectby() function that handles traversing trees. http://www.postgresql.org/docs/8.3/interactive/tablefunc.html

• Also check out the ltree contrib, which you could adapt your table to use: http://www.postgresql.org/docs/8.3/interactive/ltree.html

• Or you can traverse the tree yourself with a PL/PGSQL function.

Something like this:

create or replace function example_subtree (integer)
returns setof example as
'declare results record;
         child record;
 begin
  select into results * from example where parent_id = $1;
  if found then
    return next results;
    for child in select id from example
                  where parent_id = $1
      loop
        for temp in select * from example_subtree(child.id)
        loop
          return next temp;
        end loop;
      end loop;
  end if;
  return null;
end;' language 'plpgsql';

select sum(value) as value_sum
  from example_subtree(1234);

---

Related Topics