CODEWITHSUNDEEP
mysqlphpmyadminrelational-database
alancussen
alancussen

Reputation: 47

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

Upvotes: 1

Views: 85

Answers (1)

philipxy
philipxy

Reputation: 15118

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 c1.ID <> c2.ID
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 with no duplicates holds (the set of) rows that make a true statement from a statement template (predicate) 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
ON c1.ID <> c2.ID
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

Conditions can be in ANDed any order as long as each only uses 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.)

DISTINCT removes duplicate rows after dropping the non-SELECTED columns from the table that the FROM etc produces. This is so that the result holds the set of rows that satisfy its template. DISTINCT isn't always necessary. But you still want distinct rows. In general you have to think about whether you can avoid DISTINCT. Sometimes you can't. Sometimes you can reason that what you do next with a table with duplicates gives the same answer whether or not there are duplicates. Rarely, the requested result is permitted to have or is supposed to have duplicates. But then you can't use that result further while reasoning via the simple relational-template expression correspondence. (Exercise: Show whether there is a SELECT returning the right classId & className values from the FROM etc table without DISTINCT.)

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

Upvotes: 1

Related Questions