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?
One Solution collect form web for “Recursive SQL Server query”
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;