Joins and Subqueries

This category deals primarily with a foundational concept in relational database systems: joining. Joining allows you to combine related information from multiple tables to answer a question. This isn’t just beneficial for ease of querying: a lack of join capability encourages denormalisation of data, which increases the complexity of keeping your data internally consistent.

This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries).

Retrieve the start times of members’ bookings

How can you produce a list of the start times for bookings by members named ‘David Farrell’?

  1. SELECT starttime FROM bookings
  2. INNER JOIN members ON (bookings.memid = members.memid)
  3. WHERE surname = 'Farrell' AND firstname = 'David';
  1. query = (Booking
  2.          .select(Booking.starttime)
  3.          .join(Member)
  4.          .where((Member.surname == 'Farrell') &
  5.                 (Member.firstname == 'David')))

Work out the start times of bookings for tennis courts

How can you produce a list of the start times for bookings for tennis courts, for the date ‘2012-09-21’? Return a list of start time and facility name pairings, ordered by the time.

  1. SELECT starttime, name
  2. FROM bookings
  3. INNER JOIN facilities ON (bookings.facid = facilities.facid)
  4. WHERE date_trunc('day', starttime) = '2012-09-21':: date
  5.   AND name ILIKE 'tennis%'
  6. ORDER BY starttime, name;
  1. query = (Booking
  2.          .select(Booking.starttime, Facility.name)
  3.          .join(Facility)
  4.          .where(
  5.              (fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 21)) &
  6.              Facility.name.startswith('Tennis'))
  7.          .order_by(Booking.starttime, Facility.name))
  9. # To retrieve the joined facility's name when iterating:
  10. for booking in query:
  11.     print(booking.starttime, booking.facility.name)

Produce a list of all members who have recommended another member

How can you output a list of all members who have recommended another member? Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname).

  1. SELECT DISTINCT m.firstname, m.surname
  2. FROM members AS m2
  3. INNER JOIN members AS m ON (m.memid = m2.recommendedby)
  4. ORDER BY m.surname, m.firstname;
  1. MA = Member.alias()
  2. query = (Member
  3.          .select(Member.firstname, Member.surname)
  4.          .join(MA, on=(MA.recommendedby == Member.memid))
  5.          .order_by(Member.surname, Member.firstname))

Produce a list of all members, along with their recommender

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).

  1. SELECT m.firstname, m.surname, r.firstname, r.surname
  2. FROM members AS m
  3. LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid)
  4. ORDER BY m.surname, m.firstname
  1. MA = Member.alias()
  2. query = (Member
  3.          .select(Member.firstname, Member.surname, MA.firstname, MA.surname)
  4.          .join(MA, JOIN.LEFT_OUTER, on=(Member.recommendedby == MA.memid))
  5.          .order_by(Member.surname, Member.firstname))
  7. # To display the recommender's name when iterating:
  8. for m in query:
  9.     print(m.firstname, m.surname)
  10.     if m.recommendedby:
  11.         print('  ', m.recommendedby.firstname, m.recommendedby.surname)

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

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.

  1. SELECT DISTINCT m.firstname || ' ' || m.surname AS member, f.name AS facility
  2. FROM members AS m
  3. INNER JOIN bookings AS b ON (m.memid = b.memid)
  4. INNER JOIN facilities AS f ON (b.facid = f.facid)
  5. WHERE f.name LIKE 'Tennis%'
  6. ORDER BY member, facility;
  1. fullname = Member.firstname + ' ' + Member.surname
  2. query = (Member
  3.          .select(fullname.alias('member'), Facility.name.alias('facility'))
  4.          .join(Booking)
  5.          .join(Facility)
  6.          .where(Facility.name.startswith('Tennis'))
  7.          .order_by(fullname, Facility.name)
  8.          .distinct())

Produce a list of costly bookings

How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour ‘slot’), and the guest user is always ID 0. Include in your output the name of the facility, the name of the member formatted as a single column, and the cost. Order by descending cost, and do not use any subqueries.

  1. SELECT m.firstname || ' ' || m.surname AS member,
  2.        f.name AS facility,
  3.        (CASE WHEN m.memid = 0 THEN f.guestcost * b.slots
  4.         ELSE f.membercost * b.slots END) AS cost
  5. FROM members AS m
  6. INNER JOIN bookings AS b ON (m.memid = b.memid)
  7. INNER JOIN facilities AS f ON (b.facid = f.facid)
  8. WHERE (date_trunc('day', b.starttime) = '2012-09-14') AND
  9.  ((m.memid = 0 AND b.slots * f.guestcost > 30) OR
  10.   (m.memid > 0 AND b.slots * f.membercost > 30))
  11. ORDER BY cost DESC;
  1. cost = Case(Member.memid, (
  2.     (0, Booking.slots * Facility.guestcost),
  3. ), (Booking.slots * Facility.membercost))
  4. fullname = Member.firstname + ' ' + Member.surname
  6. query = (Member
  7.          .select(fullname.alias('member'), Facility.name.alias('facility'),
  8.                  cost.alias('cost'))
  9.          .join(Booking)
  10.          .join(Facility)
  11.          .where(
  12.              (fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)) &
  13.              (cost > 30))
  14.          .order_by(SQL('cost').desc()))
  16. # To iterate over the results, it might be easiest to use namedtuples:
  17. for row in query.namedtuples():
  18.     print(row.member, row.facility, row.cost)

Produce a list of all members, along with their recommender, using no joins.

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.

  1. SELECT DISTINCT m.firstname || ' ' || m.surname AS member,
  2.    (SELECT r.firstname || ' ' || r.surname
  3.     FROM cd.members AS r
  4.     WHERE m.recommendedby = r.memid) AS recommended
  5. FROM members AS m ORDER BY member;
  1. MA = Member.alias()
  2. subq = (MA
  3.         .select(MA.firstname + ' ' + MA.surname)
  4.         .where(Member.recommendedby == MA.memid))
  5. query = (Member
  6.          .select(fullname.alias('member'), subq.alias('recommended'))
  7.          .order_by(fullname))

Produce a list of costly bookings, using a subquery

The “Produce a list of costly bookings” exercise contained some messy logic: we had to calculate the booking cost in both the WHERE clause and the CASE statement. Try to simplify this calculation using subqueries.

  1. SELECT member, facility, cost from (
  2.   SELECT
  3.   m.firstname || ' ' || m.surname as member,
  4.   f.name as facility,
  5.   CASE WHEN m.memid = 0 THEN b.slots * f.guestcost
  6.   ELSE b.slots * f.membercost END AS cost
  7.   FROM members AS m
  8.   INNER JOIN bookings AS b ON m.memid = b.memid
  9.   INNER JOIN facilities AS f ON b.facid = f.facid
  10.   WHERE date_trunc('day', b.starttime) = '2012-09-14'
  11. ) as bookings
  12. WHERE cost > 30
  13. ORDER BY cost DESC;
  1. cost = Case(Member.memid, (
  2.     (0, Booking.slots * Facility.guestcost),
  3. ), (Booking.slots * Facility.membercost))
  5. iq = (Member
  6.       .select(fullname.alias('member'), Facility.name.alias('facility'),
  7.               cost.alias('cost'))
  8.       .join(Booking)
  9.       .join(Facility)
  10.       .where(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)))
  12. query = (Member
  13.          .select(iq.c.member, iq.c.facility, iq.c.cost)
  14.          .from_(iq)
  15.          .where(iq.c.cost > 30)
  16.          .order_by(SQL('cost').desc()))
  18. # To iterate, try using dicts:
  19. for row in query.dicts():
  20.     print(row['member'], row['facility'], row['cost'])