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.
Schema reminder
DB schema

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 Hint Help Save Run Query

 

Answers and Discussion Show

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.

Read up on WITH RECURSIVE.

Keyboard shortcuts:


Other hints: