0

This question is a extension of a previous question Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER.

From that I know how to do a CONNECT BY in SQL Server. In Oracle you can use a connect by easily in a sub query.

So in Oracle I can do select * from t1 where t1.id in (select from connect by query).

Is that also possible in SQL server? How?

Community
  • 1
  • 1
onknows
  • 6,151
  • 12
  • 65
  • 109

1 Answers1

1

In SQL Server (and other databases as well) you will need to use a recursive common table expression, something like:

with some_tree as ( 
    select ...
    union all
    select ...
)
select * 
from t1 
where t1.id in (select some_id from some_tree);

Since version 11.2 Oracle does also support recursive common table expresions (so you could use the same syntax on both databases)