Home / Joins and Subqueries / Produce a list of all members, along with their recommender, using no joins.
Produce a list of all members, along with their recommender, using no joins.
Question
How can you output a list of all members, including the individual who recommended them (if any), without using any joins? Ensure that there are no duplicates in the list, and that each firstname + surname pairing is formatted as a column and ordered.
Expected Results
| member | recommender |
|---|---|
| Anna Mackenzie | Darren Smith |
| Anne Baker | Ponder Stibbons |
| Burton Tracy | NULL |
| Charles Owen | Darren Smith |
| Darren Smith | NULL |
| David Farrell | NULL |
| David Jones | Janice Joplette |
| David Pinker | Jemima Farrell |
| Douglas Jones | David Jones |
| Erica Crumpet | Tracy Smith |
| Florence Bader | Ponder Stibbons |
| GUEST GUEST | NULL |
| Gerald Butters | Darren Smith |
| Henrietta Rumney | Matthew Genting |
| Henry Worthington-Smyth | Tracy Smith |
| Hyacinth Tupperware | NULL |
| Jack Smith | Darren Smith |
| Janice Joplette | Darren Smith |
| Jemima Farrell | NULL |
| Joan Coplin | Timothy Baker |
| John Hunt | Millicent Purview |
| Matthew Genting | Gerald Butters |
| Millicent Purview | Tracy Smith |
| Nancy Dare | Janice Joplette |
| Ponder Stibbons | Burton Tracy |
| Ramnaresh Sarwin | Florence Bader |
| Tim Boothe | Tim Rownam |
| Tim Rownam | NULL |
| Timothy Baker | Jemima Farrell |
| Tracy Smith | NULL |
Your Answer
Your Results
Loading database...
Answers and Discussion
select distinct mems.firstname || ' ' || mems.surname as member,
(select recs.firstname || ' ' || recs.surname as recommender
from cd.members recs
where recs.memid = mems.recommendedby
)
from
cd.members mems
order by member;This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'.
In this case, we're simply using the subquery to emulate an outer join. For every value of member, the subquery is run once to find the name of the individual who recommended them (if any). A subquery that uses information from the outer query in this way (and thus has to be run for each row in the result set) is known as a correlated subquery.