StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>EDIT</strong> based on OP's table:</p> <p>here is an example using the columns from the OP's table definition, my sample data is as follows:</p> <pre><code> 1-Jerome | 2-Joe / \ 3-Paul 6-David / \ / \ 4-Jack 5-Daniel 7-Ian 8-Helen --I only included the needed columns from the OP's table here DECLARE @Contacts table (id varchar(36), first_name varchar(100), reports_to_id varchar(36)) INSERT @Contacts VALUES ('1','Jerome', NULL ) INSERT @Contacts VALUES ('2','Joe' ,'1') INSERT @Contacts VALUES ('3','Paul' ,'2') INSERT @Contacts VALUES ('4','Jack' ,'3') INSERT @Contacts VALUES ('5','Daniel','3') INSERT @Contacts VALUES ('6','David' ,'2') INSERT @Contacts VALUES ('7','Ian' ,'6') INSERT @Contacts VALUES ('8','Helen' ,'6') DECLARE @Root_id char(4) --get complete tree--------------------------------------------------- SET @Root_id=null PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null') ;WITH StaffTree AS ( SELECT c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf FROM @Contacts c LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL) UNION ALL SELECT s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1 FROM StaffTree t INNER JOIN @Contacts s ON t.id=s.reports_to_id WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1 ) SELECT * FROM StaffTree --get all below 2--------------------------------------------------- SET @Root_id=2 PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null') ;WITH StaffTree AS ( SELECT c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf FROM @Contacts c LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL) UNION ALL SELECT s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1 FROM StaffTree t INNER JOIN @Contacts s ON t.id=s.reports_to_id WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1 ) SELECT * FROM StaffTree --get all below 6--------------------------------------------------- SET @Root_id=6 PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null') ;WITH StaffTree AS ( SELECT c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf FROM @Contacts c LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL) UNION ALL SELECT s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1 FROM StaffTree t INNER JOIN @Contacts s ON t.id=s.reports_to_id WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1 ) SELECT * FROM StaffTree </code></pre> <p>OUTPUT:</p> <pre><code>@Root_id=null id first_name reports_to_id Manager_id Manager_first_name LevelOf ------ ---------- ------------- ---------- ------------------ ----------- 1 Jerome NULL NULL NULL 1 2 Joe 1 1 Jerome 2 3 Paul 2 2 Joe 3 6 David 2 2 Joe 3 7 Ian 6 6 David 4 8 Helen 6 6 David 4 4 Jack 3 3 Paul 4 5 Daniel 3 3 Paul 4 (8 row(s) affected) @Root_id='2 ' id first_name reports_to_id Manager_id Manager_first_name LevelOf ------ ---------- ------------- ---------- ------------------ ----------- 2 Joe 1 1 Jerome 1 3 Paul 2 2 Joe 2 6 David 2 2 Joe 2 7 Ian 6 6 David 3 8 Helen 6 6 David 3 4 Jack 3 3 Paul 3 5 Daniel 3 3 Paul 3 (7 row(s) affected) @Root_id='6 ' id first_name reports_to_id Manager_id Manager_first_name LevelOf ------ ---------- ------------- ---------- ------------------ ----------- 6 David 2 2 Joe 1 7 Ian 6 6 David 2 8 Helen 6 6 David 2 (3 row(s) affected) </code></pre>

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