Produce a list of all members, along with their recommender

Question

How can you output a list of all members, including the individual who recommended them (if any)? Ensure that results are ordered by (surname, firstname).

Expected Results

memfname	memsname	recfname	recsname
Florence	Bader	Ponder	Stibbons
Anne	Baker	Ponder	Stibbons
Timothy	Baker	Jemima	Farrell
Tim	Boothe	Tim	Rownam
Gerald	Butters	Darren	Smith
Joan	Coplin	Timothy	Baker
Erica	Crumpet	Tracy	Smith
Nancy	Dare	Janice	Joplette
David	Farrell	NULL	NULL
Jemima	Farrell	NULL	NULL
GUEST	GUEST	NULL	NULL
Matthew	Genting	Gerald	Butters
John	Hunt	Millicent	Purview
David	Jones	Janice	Joplette
Douglas	Jones	David	Jones
Janice	Joplette	Darren	Smith
Anna	Mackenzie	Darren	Smith
Charles	Owen	Darren	Smith
David	Pinker	Jemima	Farrell
Millicent	Purview	Tracy	Smith
Tim	Rownam	NULL	NULL
Henrietta	Rumney	Matthew	Genting
Ramnaresh	Sarwin	Florence	Bader
Darren	Smith	NULL	NULL
Darren	Smith	NULL	NULL
Jack	Smith	Darren	Smith
Tracy	Smith	NULL	NULL
Ponder	Stibbons	Burton	Tracy
Burton	Tracy	NULL	NULL
Hyacinth	Tupperware	NULL	NULL
Henry	Worthington-Smyth	Tracy	Smith

Your Answer

Your Results

Loading database...

Answers and Discussion

select mems.firstname as memfname, mems.surname as memsname, recs.firstname as recfname, recs.surname as recsname
	from 
		cd.members mems
		left outer join cd.members recs
			on recs.memid = mems.recommendedby
order by memsname, memfname;

Let's introduce another new concept: the LEFT OUTER JOIN. These are best explained by the way in which they differ from inner joins. Inner joins take a left and a right table, and look for matching rows based on a join condition (ON). When the condition is satisfied, a joined row is produced. A LEFT OUTER JOIN operates similarly, except that if a given row on the left hand table doesn't match anything, it still produces an output row. That output row consists of the left hand table row, and a bunch of NULLS in place of the right hand table row.

This is useful in situations like this question, where we want to produce output with optional data. We want the names of all members, and the name of their recommender if that person exists. You can't express that properly with an inner join.

As you may have guessed, there's other outer joins too. The RIGHT OUTER JOIN is much like the LEFT OUTER JOIN, except that the left hand side of the expression is the one that contains the optional data. The rarely-used FULL OUTER JOIN treats both sides of the expression as optional.

← Produce a list of all members who have recommended another member

Home / Joins and Subqueries

Produce a list of all members who have used a tennis court →