StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can use Common Table Expressions (CTEs) to solve this problem. CTEs can be used for recursion, as Andrei pointed out (see the excellent reference that Andrei included in his post). Let's say you have a table as follows:</p> <pre><code>create table Person ( PersonId int primary key, Name varchar(25), ManagerId int foreign Key references Person(PersonId) ) </code></pre> <p>and let's insert the following data into the table:</p> <pre><code>insert into Person (PersonId, Name, ManagerId) values (1,'Bob', null), (2, 'Steve',1), (3, 'Tim', 2) (4, 'John', 3), (5, 'James', null), (6, 'Joe', 5) </code></pre> <p>then we want a query that will return everyone who directly or indirectly reports to Bob, which would be Steve, Tim and John. We don't want to return James and Bob, since they report to no one, or Joe, since he reports to James. This can be done with a CTE query as follows:</p> <pre><code>WITH Managers AS ( --initialize SELECT PersonId, Name, ManagerId FROM Person WHERE ManagerId =1 UNION ALL --recursion SELECT p.PersonId, p.Name, p.ManagerId FROM Person p INNER JOIN Managers m ON p.ManagerId = m.PersonId ) SELECT * FROM Managers </code></pre> <p>This query returns the correct results:</p> <pre><code>PersonId Name ManagerId ----------- ------------------------- ----------- 2 Steve 1 3 Tim 2 4 John 3 </code></pre> <p><strong>Edit:</strong> This answer is valid assuming the OP is using SQL Server 2005 or higher. I do not know if this syntax is valid in MySQL or Oracle.</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