Joins and Subqueries

This category deals primarily with a foundational concept in relationaldatabase systems: joining. Joining allows you to combine related informationfrom multiple tables to answer a question. This isn’t just beneficial for easeof 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 littletime 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’?

SELECT starttime FROM bookings
INNER JOIN members ON (bookings.memid = members.memid)
WHERE surname = 'Farrell' AND firstname = 'David';

query = (Booking
         .select(Booking.starttime)
         .join(Member)
         .where((Member.surname == 'Farrell') &
                (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 namepairings, ordered by the time.

SELECT starttime, name
FROM bookings
INNER JOIN facilities ON (bookings.facid = facilities.facid)
WHERE date_trunc('day', starttime) = '2012-09-21':: date
  AND name ILIKE 'tennis%'
ORDER BY starttime, name;

query = (Booking
         .select(Booking.starttime, Facility.name)
         .join(Facility)
         .where(
             (fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 21)) &
             Facility.name.startswith('Tennis'))
         .order_by(Booking.starttime, Facility.name))
 
# To retrieve the joined facility's name when iterating:
for booking in query:
    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 orderedby (surname, firstname).

SELECT DISTINCT m.firstname, m.surname
FROM members AS m2
INNER JOIN members AS m ON (m.memid = m2.recommendedby)
ORDER BY m.surname, m.firstname;

MA = Member.alias()
query = (Member
         .select(Member.firstname, Member.surname)
         .join(MA, on=(MA.recommendedby == Member.memid))
         .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 whorecommended them (if any)? Ensure that results are ordered by (surname,firstname).

SELECT m.firstname, m.surname, r.firstname, r.surname
FROM members AS m
LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid)
ORDER BY m.surname, m.firstname

MA = Member.alias()
query = (Member
         .select(Member.firstname, Member.surname, MA.firstname, MA.surname)
         .join(MA, JOIN.LEFT_OUTER, on=(Member.recommendedby == MA.memid))
         .order_by(Member.surname, Member.firstname))
 
# To display the recommender's name when iterating:
for m in query:
    print(m.firstname, m.surname)
    if m.recommendedby:
        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 memberformatted as a single column. Ensure no duplicate data, and order by themember name.

SELECT DISTINCT m.firstname || ' ' || m.surname AS member, f.name AS facility
FROM members AS m
INNER JOIN bookings AS b ON (m.memid = b.memid)
INNER JOIN facilities AS f ON (b.facid = f.facid)
WHERE f.name LIKE 'Tennis%'
ORDER BY member, facility;

fullname = Member.firstname + ' ' + Member.surname
query = (Member
         .select(fullname.alias('member'), Facility.name.alias('facility'))
         .join(Booking)
         .join(Facility)
         .where(Facility.name.startswith('Tennis'))
         .order_by(fullname, Facility.name)
         .distinct())

Produce a list of costly bookings

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

SELECT m.firstname || ' ' || m.surname AS member,
       f.name AS facility,
       (CASE WHEN m.memid = 0 THEN f.guestcost * b.slots
        ELSE f.membercost * b.slots END) AS cost
FROM members AS m
INNER JOIN bookings AS b ON (m.memid = b.memid)
INNER JOIN facilities AS f ON (b.facid = f.facid)
WHERE (date_trunc('day', b.starttime) = '2012-09-14') AND
 ((m.memid = 0 AND b.slots * f.guestcost > 30) OR
  (m.memid > 0 AND b.slots * f.membercost > 30))
ORDER BY cost DESC;

cost = Case(Member.memid, (
    (0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost))
fullname = Member.firstname + ' ' + Member.surname
 
query = (Member
         .select(fullname.alias('member'), Facility.name.alias('facility'),
                 cost.alias('cost'))
         .join(Booking)
         .join(Facility)
         .where(
             (fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)) &
             (cost > 30))
         .order_by(SQL('cost').desc()))
 
# To iterate over the results, it might be easiest to use namedtuples:
for row in query.namedtuples():
    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 whorecommended them (if any), without using any joins? Ensure that there are noduplicates in the list, and that each firstname + surname pairing isformatted as a column and ordered.

SELECT DISTINCT m.firstname || ' ' || m.surname AS member,
   (SELECT r.firstname || ' ' || r.surname
    FROM cd.members AS r
    WHERE m.recommendedby = r.memid) AS recommended
FROM members AS m ORDER BY member;

MA = Member.alias()
subq = (MA
        .select(MA.firstname + ' ' + MA.surname)
        .where(Member.recommendedby == MA.memid))
query = (Member
         .select(fullname.alias('member'), subq.alias('recommended'))
         .order_by(fullname))

Produce a list of costly bookings, using a subquery

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

SELECT member, facility, cost from (
  SELECT
  m.firstname || ' ' || m.surname as member,
  f.name as facility,
  CASE WHEN m.memid = 0 THEN b.slots * f.guestcost
  ELSE b.slots * f.membercost END AS cost
  FROM members AS m
  INNER JOIN bookings AS b ON m.memid = b.memid
  INNER JOIN facilities AS f ON b.facid = f.facid
  WHERE date_trunc('day', b.starttime) = '2012-09-14'
) as bookings
WHERE cost > 30
ORDER BY cost DESC;

cost = Case(Member.memid, (
    (0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost))
 
iq = (Member
      .select(fullname.alias('member'), Facility.name.alias('facility'),
              cost.alias('cost'))
      .join(Booking)
      .join(Facility)
      .where(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)))
 
query = (Member
         .select(iq.c.member, iq.c.facility, iq.c.cost)
         .from_(iq)
         .where(iq.c.cost > 30)
         .order_by(SQL('cost').desc()))
 
# To iterate, try using dicts:
for row in query.dicts():
    print(row['member'], row['facility'], row['cost'])