CODEWITHSUNDEEP
sqlsql-serverrecursion
doque
doque

Reputation: 2733

Recursive SQL Server query

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?

Upvotes: 2

Views: 1468

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

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

Upvotes: 6

Related Questions