Simulation of CONNECT BY PRIOR of Oracle in SQL Server

Question

How can I get the functionality of CONNECT BY PRIOR of Oracle in SQL Server 2000/2005/2008?

Answer 1

The SQL standard way to implement recursive queries, as implemented e.g. by IBM DB2 and SQL Server, is the WITH clause. See this article for one example of translating a CONNECT BY into a WITH (technically a recursive CTE) -- the example is for DB2 but I believe it will work on SQL Server as well.

Edit: apparently the original querant requires a specific example, here's one from the IBM site whose URL I already gave. Given a table:

CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY,
                 name   VARCHAR(10),
                 salary DECIMAL(9, 2),
                 mgrid  INTEGER);

where mgrid references an employee's manager's empid, the task is, get the names of everybody who reports directly or indirectly to Joan. In Oracle, that's a simple CONNECT:

SELECT name 
  FROM emp
  START WITH name = 'Joan'
  CONNECT BY PRIOR empid = mgrid

In SQL Server, IBM DB2, or PostgreSQL 8.4 (as well as in the SQL standard, for what that's worth;-), the perfectly equivalent solution is instead a recursive query (more complex syntax, but, actually, even more power and flexibility):

WITH n(empid, name) AS 
   (SELECT empid, name 
    FROM emp
    WHERE name = 'Joan'
        UNION ALL
    SELECT nplus1.empid, nplus1.name 
    FROM emp as nplus1, n
    WHERE n.empid = nplus1.mgrid)
SELECT name FROM n

Oracle's START WITH clause becomes the first nested SELECT, the base case of the recursion, to be UNIONed with the recursive part which is just another SELECT.

SQL Server's specific flavor of WITH is of course documented on MSDN, which also gives guidelines and limitations for using this keyword, as well as several examples.

Answer 2

@Alex Martelli's answer is great! But it work only for one element at time (WHERE name = 'Joan') If you take out the WHERE clause, the query will return all the root rows together...

I changed a little bit for my situation, so it can show the entire tree for a table.

table definition:

CREATE TABLE [dbo].[mar_categories] ( 
    [category]  int IDENTITY(1,1) NOT NULL,
    [name]      varchar(50) NOT NULL,
    [level]     int NOT NULL,
    [action]    int NOT NULL,
    [parent]    int NULL,
    CONSTRAINT [XPK_mar_categories] PRIMARY KEY([category])
)

(level is literally the level of a category 0: root, 1: first level after root, ...)

and the query:

WITH n(category, name, level, parent, concatenador) AS 
(
    SELECT category, name, level, parent, '('+CONVERT(VARCHAR (MAX), category)+' - '+CONVERT(VARCHAR (MAX), level)+')' as concatenador
    FROM mar_categories
    WHERE parent is null
        UNION ALL
    SELECT m.category, m.name, m.level, m.parent, n.concatenador+' * ('+CONVERT (VARCHAR (MAX), case when ISNULL(m.parent, 0) = 0 then 0 else m.category END)+' - '+CONVERT(VARCHAR (MAX), m.level)+')' as concatenador
    FROM mar_categories as m, n
    WHERE n.category = m.parent
)
SELECT distinct * FROM n ORDER BY concatenador asc

(You don't need to concatenate the level field, I did just to make more readable)

the answer for this query should be something like:

I hope it helps someone!

now, I'm wondering how to do this on MySQL... ^^

Answer 3

I haven't used connect by prior, but a quick search shows it's used for tree structures. In SQL Server, you use common table expressions to get similar functionality.