Recursive SQL Server query

Question

In a table reviewers with a structure like this:

reviewer | reviewee
===================
2        |      1
3        |      2
4        |      3
5        |      4

In a function call, I know both a reviewer-id and a reviewee-id (the owner of the item the reviewee is looking to retrieve).

I'm now trying to send a query that iterates all the entries in the reviewers table, starting with the reviewer, and ends at the reviewee's id (and matches that to the reviewee id I know). So I'm trying to find out if there is a connection between reviewee and reviewer at all.

Is it possible to do this in a single query?

Community · Accepted Answer · 2021-01-18 12:30:34Z

6

You can do this:

WITH CTE
AS
(
   SELECT reviewer, reviewee
   FROM TableName 
   WHERE reviewee = @revieweeID
   UNION ALL
   SELECT p.reviewer, p.reviewee 
   FROM CTE c
   INNER JOIN TableName p ON c.reviewee = p.reviewer
)
SELECT * 
FROM CTE;
--- WHERE reviewer = @reviewerID;

Demo

edited Jan 18, 2021 at 12:30

CommunityBot

11 silver badge

answered Dec 18, 2012 at 12:46

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

You should start putting the statement terminator (semicolon) at the end of the statement (where it belongs), not at the start: sqlskills.com/blogs/bobb/post/… or sqlblog.org/2009/09/03/…

Collectives™ on Stack Overflow

Recursive SQL Server query

1 Answer 1

Demo

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related