alancussen alancussen - 22 days ago 15
MySQL Question

SQL: Returning a list of all the classes that 2 specific students are both attending

I've tried all I can to figure this one out, hopefully someone here can help.



I'm given the database schema as follows:


Customer: ID FirstName LastName DateOfBirth

Instructor: Id FirstName LastName

FitnessClass: Id ClassName Cost InstructorId

Subscription: CustomerId ClassId StartDate


With the question being:



Retrieve a list of all classes (Id and ClassName) that both Joe Bloggs and John Snow have subscribed to (ie Joe Bloggs and John Snow have been in the same Fitness Class).


My attempts have only produced all classes that either are in, not only the classes that both are in.


My attempt:

SELECT fitnessclass.id, fitnessclass.classname
FROM fitnessclass
LEFT JOIN subscription ON fitnessclass.Id = subscription.ClassID
LEFT JOIN customer ON subscription.CustomerID = customer.ID
WHERE customer.ID IS NOT NULL
AND customer.FirstName IN ("Joe", "Bloggs")

Answer

We want the c.ClassId and c.ClassName values from DISTINCT rows where:

    class c.Id is named c.ClassName and costs c.Cost and is taught by c.InstructorId
AND customer c1.ID is named c1.FirstName c1.LastName and was born on c1.DateOfBirth
AND customer c2.ID is named c2.FirstName c2.LastName and was born on c2.DateOfBirth
AND customer s1.CustomerId subscribed to s1.ClassId starting on s1.StartDate
AND customer s2.CustomerId subscribed to s2.ClassId starting on s2.StartDate
AND c1.ID = s1.CustomerId AND c2.ID = s2.CustomerId
AND c1.FirstName = 'Joe' AND c1.LastName = 'Bloggs'
AND c2.FirstName = 'John' AND c2.LastName = 'Snow'
AND s1.ClassId = c.ClassId AND s2.ClassId = c.ClassId

Observe that an aliased table holds rows that make a true statement from a statement template associated with its base table by the database designer:

--  class c.Id is named c.ClassName and costs c.Cost and is taught by c.instructorId
FitnessClass c

-- cx.ID is named cx.FirstName cx.LastName and was born on cx.DateOfBirth
Customer cx

-- customer sx.CustomerId subscribed to class sx.ClassId starting on sx.StartDate
Subscription sx

Observe also that if expression L holds the rows satisfying templateL and expression R holds the rows satisfying templateR then

  • L JOIN R holds the rows satisfying templateL AND templateR
  • R WHERE condition holds the rows satisfying templateR AND condition
  • templateR ON condition holds the rows satisfying templateR AND condition

So the rows we want to SELECT from are:

FROM Class c
JOIN Customer c1 JOIN Customer c2
JOIN Subscription s1 JOIN Subscription s2
WHERE
AND c1.ID = s1.CustomerId AND c2.ID = s2.CustomerId
AND c1.FirstName = 'Joe' AND c1.LastName = 'Bloggs'
AND c2.FirstName = 'John' AND c2.LastName = 'Snow'
AND s1.ClassId = c.ClassId AND s2.ClassId = c.ClassId

AND and JOIN and JOIN ON can be called in any order as long as conditions mention only columns from a preceding JOIN or JOIN ON. So you can rearrange these in some other order if you think it better. (Eg to localize the use of some column names.) (But arguments that you must organize via ON (or that "," is inappropriate) are specious.)

(It's not clear why you think that LEFT JOIN is appropriate. It returns what JOIN does but with unmatched left table rows extended by NULLs.)