sql - 如何从另一个 SQL 表中获取两个不同列的匹配数据 : Inner Join and/or Union?

Question

我在 MS Access 中有两个表，用于跟踪类(class)辅导员和他们主持的类(class)。两个表的结构如下:

tbl_facilitators

facilID -> a unique autonumber to keep track of individual teachers
facilLname -> the Last name of the facilitator
facilFname -> the First name of the facilitator

tbl_facilitatorClasses

classID -> a unique autonumber to keep track of individual classes
className -> the name of the class (science, math, etc)
primeFacil -> the facilID from the first table of a teacher who is primary facilitator
secondFacil -> the facilID  from the first table of another teacher who is backup facilitator

我不知道如何编写以这种格式提取结果的内部联接:

Column 1:  Class Name
Column 2:  Primary Facilitator's Last Name
Column 3:  Primary Facilitator's First Name
Column 4:  Secondary Facilitator's Last Name
Column 5:  Secondary Facilitator's First Name

如果我只请求主要协助者本身或只请求次要协助者本身，我就能够得到正确的结果。不过，我无法让他们都锻炼。

这是我的工作内部连接:

SELECT tbl_facilitatorClasses.className,
    tbl_facilitators.facilLname, tbl_facilitators.facilFname
FROM tbl_facilitatorClasses
INNER JOIN tbl_facilitators
ON tbl_facilitatorClasses.primeFacil = tbl_facilitators.facilID;

出于绝望，我也尝试了一个 Union，但它并没有像我希望的那样成功。非常感谢您的帮助。在这一点上，我真的很难取得任何进展。我不经常使用 SQL。

解决方案

感谢@philipxy，我提出了以下最终有效的查询:

SELECT tblCLS.className,
    tblP.facilLname, tblP.facilFname, tblS.facilLname, tblS.facilFname
FROM (tbl_facilitatorClasses AS tblCLS
INNER JOIN tbl_facilitators AS tblP
ON tblCLS.primeFacil=tblP.facilID)
INNER JOIN tbl_facilitators AS tblS
ON tblCLS.secondFacil=tblS.facilID;

在 MS Access 中执行多个内部联接时，需要括号... As described in this other post.

Answer 1

(当每一行都是 SQL DISTINCT 并且外部 SQL 代码同样将 NULL 视为另一个值时，以下内容适用。)

每个基表都有一个语句模板，又名 predicate，由列名参数化，我们可以通过它来插入或删除一行。我们可以对谓词使用(标准谓词逻辑)简写，就像它的 SQL 声明一样。

-- facilitator [facilID] is named [facilFname] [facilLname]
facilitator(facilID, facilLname, facilFname)
-- class [classID] named [className] has prime [primeFacil] & backup [secondFacil]
class(classID, className, primeFacil, secondFacil)

将一行插入谓词给出一个陈述，也就是命题。构成真命题的行进入表格，而构成假命题的行则被排除在外。 (因此，表格说明了每个存在行的命题 和 不是每个不存在行的命题。)

-- facilitator f1 is named Jane Doe
facilitator(f1, 'Jane', 'Doe')
-- class c1 named CSC101 has prime f1 & backup f8
class(c1, 'CSC101', f1, f8)

但是每个表表达式值 的每个表达式都有一个谓词。 SQL 的设计使得如果表 T 和 U 包含(无 NULL 非重复)行，其中 T(...) 和 U(...) (分别)然后:

• T CROSS JOIN U 包含 T(...) AND U(...) 的行
• T INNER JOIN U ONcondition 包含 T(...) AND U(...) AND 的行条件
• T LEFT JOIN U ONcondition 保存行，其中(对于 U-only 列 U1,...)
  T(...) AND U(...) AND 条件
  或者 T(...)
  并且不存在 U1 的值，... 其中 [U(...) AND condition]
  AND U1 为空且...
• T WHEREcondition 保存行，其中 T(...) AND condition
• T INTERSECT U 包含 T(...) AND U(...) 的行
• T UNION U 包含 T(...) OR U(...) 的行
• T EXCEPT U 保存 T(...) AND NOT U(...) 的行
• SELECT DISTINCT * FROM T 保存 T(...) 所在的行
• SELECT DISTINCT要保留的列FROM T 保留行，其中
  要删除的列存在 EXISTS 值，其中 T(...)
• VALUES (C1, C2, ...)((v1,v2, ...), ...) 保存行，其中
  C1 = v1 AND C2 = v2 AND ... OR ...

还有:

• (...) IN T 表示 T(...)
• scalar= T 表示 T(scalar)
• T(..., X, ...) AND X = Y 表示 T(..., Y, ...) AND X = Y

因此，为了查询，我们找到了一种使用基表谓词以自然语言表达我们想要的行的谓词的方法，然后使用基表谓词简写，然后简写使用列名中的别名(输出列除外)，然后在 SQL 中使用基表名称加上 ON 和 WHERE 条件等。如果我们需要两次提到一个基表，那么我们给它起别名。

-- natural language
there EXISTS values for classID, primeFacil & secondFacil where
    class [classID] named [className]
        has prime [primeFacil] & backup [secondFacil]
AND facilitator [primeFacil] is named [pf.facilFname] [pf.facilLname]
AND facilitator [secondFacil] is named [sf.facilFname] [sf.facilLname]

-- shorthand
there EXISTS values for classID, primeFacil & secondFacil where
    class(classID, className, primeFacil, secondFacil)
AND facilitator(pf.facilID, pf.facilLname, pf.facilFname)
AND pf.facilID = primeFacil
AND facilitator(sf.facilID, sf.facilLname, sf.facilFname)
AND sf.facilID = secondFacil

-- shorthand using aliases everywhere but result
-- use # to distinguish same-named result columns in specification
there EXISTS values for c.*, pf.*, sf.* where
    className = c.className
AND facilLname#1 = pf.facilLname AND facilFname#1 = pf.facilFname
AND facilLname#2 = sf.facilLname AND facilFname#2 = sf.facilFname
AND class(c.classID, c.className, c.primeFacil, c.secondFacil)
AND facilitator(pf.facilID, pf.facilLname, pf.facilFname)
AND pf.facilID = c.primeFacil
AND facilitator(sf.facilID, sf.facilLname, sf.facilFname)
AND sf.facilID = c.secondFacil

-- table names & SQL (with MS Access parentheses)
SELECT className, pf.facilLname, pf.facilFname, sf.facilLname, sf.facilFname
FROM (class JOIN facilitator AS pf ON pf.facilID = primeFacil)
JOIN facilitator AS sf ON sf.facilID = secondFacil

OUTER JOIN 当一个类并不总是有两个促进者或某物并不总是有所有名称时使用。 (即，如果列可以为 NULL。)但您没有为您的基表和查询提供特定谓词，也没有给出有关何时可能为 NULL 的业务规则，因此我假设没有 NULL。

Is there any rule of thumb to construct SQL query from a human-readable description?

(关于 MS Access JOIN 括号，请参阅 this from SO 和 this from MS。)