Question

How can you produce a list of all members who have used a tennis court? Include in your output the name of the court, and the name of the member formatted as a single column. Ensure no duplicate data, and order by the member name.
Schema reminder
DB schema

Expected Results

member facility
Anne Baker Tennis Court 2
Anne Baker Tennis Court 1
Burton Tracy Tennis Court 2
Burton Tracy Tennis Court 1
Charles Owen Tennis Court 2
Charles Owen Tennis Court 1
Darren Smith Tennis Court 2
David Farrell Tennis Court 2
David Farrell Tennis Court 1
David Jones Tennis Court 1
David Jones Tennis Court 2
David Pinker Tennis Court 1
Douglas Jones Tennis Court 1
Erica Crumpet Tennis Court 1
Florence Bader Tennis Court 1
Florence Bader Tennis Court 2
GUEST GUEST Tennis Court 2
GUEST GUEST Tennis Court 1
Gerald Butters Tennis Court 1
Gerald Butters Tennis Court 2
Henrietta Rumney Tennis Court 2
Jack Smith Tennis Court 1
Jack Smith Tennis Court 2
Janice Joplette Tennis Court 1
Janice Joplette Tennis Court 2
Jemima Farrell Tennis Court 2
Jemima Farrell Tennis Court 1
Joan Coplin Tennis Court 1
John Hunt Tennis Court 1
John Hunt Tennis Court 2
Matthew Genting Tennis Court 1
Millicent Purview Tennis Court 2
Nancy Dare Tennis Court 2
Nancy Dare Tennis Court 1
Ponder Stibbons Tennis Court 2
Ponder Stibbons Tennis Court 1
Ramnaresh Sarwin Tennis Court 2
Ramnaresh Sarwin Tennis Court 1
Tim Boothe Tennis Court 1
Tim Boothe Tennis Court 2
Tim Rownam Tennis Court 1
Tim Rownam Tennis Court 2
Timothy Baker Tennis Court 2
Timothy Baker Tennis Court 1
Tracy Smith Tennis Court 2
Tracy Smith Tennis Court 1

Your Answer Hint Help Save Run Query

 

Answers and Discussion Show

select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility
	from 
		cd.members mems
		inner join cd.bookings bks
			on mems.memid = bks.memid
		inner join cd.facilities facs
			on bks.facid = facs.facid
	where
		bks.facid in (0,1)
order by member          

This exercise is largely a more complex application of what you've learned in prior questions. It's also the first time we've used more than one join, which may be a little confusing for some. When reading join expressions, remember that a join is effectively a function that takes two tables, one labelled the left table, and the other the right. This is easy to visualise with just one join in the query, but a little more confusing with two.

Our second INNER JOIN in this query has a right hand side of cd.facilities. That's easy enough to grasp. The left hand side, however, is the table returned by joining cd.members to cd.bookings. It's important to emphasise this: the relational model is all about tables. The output of any join is another table. The output of a query is a table. Single columned lists are tables. Once you grasp that, you've grasped the fundamental beauty of the model.

As a final note, we do introduce one new thing here: the || operator is used to concatenate strings.

This answer requires multiple joins. To concatenate strings you can use the || operator.

Keyboard shortcuts:


Other hints: