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

Question

我已经尽我所能解决这个问题，希望这里有人能提供帮助。

我得到的数据库架构如下:

Customer: ID FirstName LastName DateOfBirth

Instructor: Id FirstName LastName

FitnessClass: Id ClassName Cost InstructorId

Subscription: CustomerId ClassId StartDate

问题是:

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

我的尝试只生成了其中一个所在的所有类，而不仅仅是两个都所在的类。

我的尝试:

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 1

我们想要 DISTINCT 行中的 c.ClassId 和 c.ClassName 值，其中:

    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

观察到一个没有重复项的别名表包含(一组)行，这些​​行从数据库设计者与其基表相关联的语句模板(谓词)中做出正确的语句:

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

还请注意，如果表达式 L 包含满足 templateL 的行并且表达式 R 包含满足 templateR 的行然后

• L JOIN R 保存满足 templateL AND templateR
的行
• R WHERE 条件 保存满足templateR AND 条件
的行
• templateR ON condition 保存满足 templateR AND condition
的行

所以我们要从中选择的行是:

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

条件可以按任意顺序进行 ANDed，只要每个条件仅使用前面的 JOIN 或 JOIN ON 中的列即可。因此，如果您认为更好，可以按其他顺序重新排列它们。 (例如，将某些列名的使用本地化。)(但是您必须通过 ON(或者“,”不合适)组织的参数是 specious。)

DISTINCT 在从 FROM 等生成的表中删除非 SELECTED 列后删除重复行。这是为了让结果包含满足其模板的行的 集。 DISTINCT 并不总是必要的。但是您仍然想要不同的行。通常，您必须 考虑是否可以避免 DISTINCT。有时你不能。有时您可以推断，无论是否存在重复项，您接下来对包含重复项的表所做的操作都会给出相同的答案。很少情况下，请求的结果被允许有或应该有重复。但是在通过简单的关系模板表达式对应进行推理时，您不能进一步使用该结果。 (练习:显示是否有 SELECT 从 FROM 等表中返回正确的 classId 和 className 值，而不使用 DISTINCT。)

(不清楚为什么您认为 LEFT JOIN 是合适的。它返回 JOIN 所做的，但不匹配的左表行由 NULL 扩展。)