StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The SQL standard way to implement recursive queries, as implemented e.g. by IBM DB2 and <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="noreferrer">SQL Server</a>, is the <code>WITH</code> clause. See <a href="http://www.ibm.com/developerworks/db2/library/techarticle/dm-0510rielau/" rel="noreferrer">this article</a> for one example of translating a <code>CONNECT BY</code> into a <code>WITH</code> (technically a <em>recursive CTE</em>) -- the example is for DB2 but I believe it will work on SQL Server as well.</p> <p>Edit: apparently the original querant requires a specific example, here's one from the IBM site whose URL I already gave. Given a table:</p> <pre><code>CREATE TABLE emp(empid INTEGER NOT NULL PRIMARY KEY, name VARCHAR(10), salary DECIMAL(9, 2), mgrid INTEGER); </code></pre> <p>where <code>mgrid</code> references an employee's manager's <code>empid</code>, the task is, get the names of everybody who reports directly or indirectly to <code>Joan</code>. In Oracle, that's a simple <code>CONNECT</code>:</p> <pre><code>SELECT name FROM emp START WITH name = 'Joan' CONNECT BY PRIOR empid = mgrid </code></pre> <p>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):</p> <pre><code>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 </code></pre> <p>Oracle's <code>START WITH</code> clause becomes the first nested <code>SELECT</code>, the base case of the recursion, to be <code>UNION</code>ed with the recursive part which is just another <code>SELECT</code>.</p> <p>SQL Server's specific flavor of <code>WITH</code> is of course documented on <a href="http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx" rel="noreferrer">MSDN</a>, which also gives guidelines and limitations for using this keyword, as well as several examples.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload