Find the downward recommendation chain for member ID 1

Question

Find the downward recommendation chain for member ID 1: that is, the members they recommended, the members those members recommended, and so on. Return member ID and name, and order by ascending member id.

Expected Results

memid	firstname	surname
4	Janice	Joplette
5	Gerald	Butters
7	Nancy	Dare
10	Charles	Owen
11	David	Jones
14	Jack	Smith
20	Matthew	Genting
21	Anna	Mackenzie
26	Douglas	Jones
27	Henrietta	Rumney

Your Answer

Your Results

Loading database...

Answers and Discussion

with recursive recommendeds(memid) as (
	select memid from cd.members where recommendedby = 1
	union all
	select mems.memid
		from recommendeds recs
		inner join cd.members mems
			on mems.recommendedby = recs.memid
)
select recs.memid, mems.firstname, mems.surname
	from recommendeds recs
	inner join cd.members mems
		on recs.memid = mems.memid
order by memid

This is a pretty minor variation on the previous question. The essential difference is that we're now heading in the opposite direction. One interesting point to note is that unlike the previous example, this CTE produces multiple rows per iteration, by virtue of the fact that we're heading down the recommendation tree (following all branches) rather than up it.

← Find the upward recommendation chain for member ID 27

Home / Recursive Queries

Produce a CTE that can return the upward recommendation chain for any member →