Find the upward recommendation chain for member ID 27
Question
Find the upward recommendation chain for member ID 27: that is, the member who recommended them, and the member who recommended that member, and so on. Return member ID, first name, and surname. Order by descending member id.
Expected Results
| recommender | firstname | surname |
|---|---|---|
| 20 | Matthew | Genting |
| 5 | Gerald | Butters |
| 1 | Darren | Smith |
Your Answer
Your Results
Loading database...
Answers and Discussion
with recursive recommenders(recommender) as (
select recommendedby from cd.members where memid = 27
union all
select mems.recommendedby
from recommenders recs
inner join cd.members mems
on mems.memid = recs.recommender
)
select recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join cd.members mems
on recs.recommender = mems.memid
order by memid descWITH RECURSIVE is a fantastically useful piece of functionality that many developers are unaware of. It allows you to perform queries over hierarchies of data, which is very difficult by other means in SQL. Such scenarios often leave developers resorting to multiple round trips to the database system.
You've seen WITH before. The Common Table Expressions (CTEs) defined by WITH give you the ability to produce inline views over your data. This is normally just a syntactic convenience, but the RECURSIVE modifier adds the ability to join against results already produced to produce even more. A recursive WITH takes the basic form of:
WITH RECURSIVE NAME(columns) as (
<initial statement>
UNION ALL
<recursive statement>
)The initial statement populates the initial data, and then the recursive statement runs repeatedly to produce more. Each step of the recursion can access the CTE, but it sees within it only the data produced by the previous iteration. It repeats until an iteration produces no additional data.
The most simple example of a recursive WITH might look something like this:
with recursive increment(num) as (
select 1
union all
select increment.num + 1 from increment where increment.num < 5
)
select * from increment;The initial statement produces '1'. The first iteration of the recursive statement sees this as the content of increment, and produces '2'. The next iteration sees the content of increment as '2', and so on. Execution terminates when the recursive statement produces no additional data.
With the basics out of the way, it's fairly easy to explain our answer here. The initial statement gets the ID of the person who recommended the member we're interested in. The recursive statement takes the results of the initial statement, and finds the ID of the person who recommended them. This value gets forwarded on to the next iteration, and so on.
Now that we've constructed the recommenders CTE, all our main SELECT statement has to do is get the member IDs from recommenders, and join to them members table to find out their names.