← Produce a list of all members, along with their recommender
Home / Joins and Subqueries / Produce a list of all members who have used a tennis court
Produce a list of costly bookings →

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

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 followed by the facility name.

Expected Results

memberfacility
Anne BakerTennis Court 1
Anne BakerTennis Court 2
Burton TracyTennis Court 1
Burton TracyTennis Court 2
Charles OwenTennis Court 1
Charles OwenTennis Court 2
Darren SmithTennis Court 2
David FarrellTennis Court 1
David FarrellTennis Court 2
David JonesTennis Court 1
David JonesTennis Court 2
David PinkerTennis Court 1
Douglas JonesTennis Court 1
Erica CrumpetTennis Court 1
Florence BaderTennis Court 1
Florence BaderTennis Court 2
GUEST GUESTTennis Court 1
GUEST GUESTTennis Court 2
Gerald ButtersTennis Court 1
Gerald ButtersTennis Court 2
Henrietta RumneyTennis Court 2
Jack SmithTennis Court 1
Jack SmithTennis Court 2
Janice JopletteTennis Court 1
Janice JopletteTennis Court 2
Jemima FarrellTennis Court 1
Jemima FarrellTennis Court 2
Joan CoplinTennis Court 1
John HuntTennis Court 1
John HuntTennis Court 2
Matthew GentingTennis Court 1
Millicent PurviewTennis Court 2
Nancy DareTennis Court 1
Nancy DareTennis Court 2
Ponder StibbonsTennis Court 1
Ponder StibbonsTennis Court 2
Ramnaresh SarwinTennis Court 1
Ramnaresh SarwinTennis Court 2
Tim BootheTennis Court 1
Tim BootheTennis Court 2
Tim RownamTennis Court 1
Tim RownamTennis Court 2
Timothy BakerTennis Court 1
Timothy BakerTennis Court 2
Tracy SmithTennis Court 1
Tracy SmithTennis Court 2

Your Answer

Your Results

Loading database...

Answers and Discussion 

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
		facs.name in ('Tennis Court 2','Tennis Court 1')
order by member, facility

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.

← Produce a list of all members, along with their recommender
Home / Joins and Subqueries
Produce a list of costly bookings →