如何从另一个 SQL 表中获取两个不同列的匹配数据:内部联接和/或联合? [英] How to get matching data from another SQL table for two different columns: Inner Join and/or Union?

问题描述

我在 MS Access 中有两个表，用于跟踪课程主持人和他们主持的课程.两个表的结构如下:

I've got two tables in MS Access that keep track of class facilitators and the classes they facilitate. The two tables are structured as follows:

tbl_facilitators

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

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

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

I cannot figure out how to write an Inner Join that pulls up the results in this format:

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

如果我仅请求主要协调人或仅请求次要协调人本身，我就能够获得正确的结果.不过，我不能让他们两个都锻炼.

I am able to pull up and get the correct results if I only request the primary facilitator by itself or only request the secondary facilitator by itself. I cannot get them both to work out, though.

这是我的工作内连接:

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

出于绝望，我也尝试了联盟，但没有如我所愿.非常感谢您的帮助.在这一点上，我真的很难取得任何进展.我不经常使用 SQL.

Out of desperation I also tried a Union, but it didn't work out as I had hoped. Your help is greatly appreciated. I'm really struggling to make any progress at this point. I don't often work with SQL.

解决方案

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

Thanks to @philipxy I came up with the following query which ended up working:

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 中执行多个内部联接时，需要括号...如上所述在另一个帖子中.

When performing multiple Inner Joins in MS Access, parenthesis are needed...As described in this other post.

推荐答案

(以下适用于每一行都是 SQL DISTINCT，并且外部 SQL 代码类似地将 NULL 视为另一个值.)

(The following applies when every row is SQL DISTINCT, and outside SQL code similarly treats NULL like just another value.)

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

Every base table has a statement template, aka predicate, parameterized by column names, by which we put a row in or leave it out. We can use a (standard predicate logic) shorthand for the predicate that is like its SQL declaration.

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

将一行插入谓词给出了一个声明，又名命题.提出真命题的行放在表中，提出假命题的行留在表中.(所以一个表格陈述了每个当前行的命题并且陈述了不是每个缺席行的命题.)

Plugging a row into a predicate gives a statement aka proposition. The rows that make a true proposition go in a table and the rows that make a false proposition stay out. (So a table states the proposition of each present row and states NOT the proposition of each absent row.)

-- 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(...) (分别)然后:

But every table expression value has a predicate per its expression. SQL is designed so that if tables T and U hold the (NULL-free non-duplicate) rows where T(...) and U(...) (respectively) then:

• 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(...)
  AND NOT there are EXISTS values for U1,... where [U(...) AND condition]
  AND U1 为空 AND ...
• 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 保存行，其中
  要删除的列存在存在值，其中 T(...)
• VALUES (C1, C2, ...)((v1,v2, ...), ...) 保存行
  C1 = v1 AND C2 = v2 AND ... OR ...

• T CROSS JOIN U holds rows where T(...) AND U(...)
• T INNER JOIN U ONcondition holds rows where T(...) AND U(...) AND condition
• T LEFT JOIN U ONcondition holds rows where (for U-only columns U1,...)
      T(...) AND U(...) AND condition
  OR T(...)
      AND NOT there EXISTS values for U1,... where [U(...) AND condition]
      AND U1 IS NULL AND ...
• T WHEREcondition holds rows where T(...) AND condition
• T INTERSECT U holds rows where T(...) AND U(...)
• T UNION U holds rows where T(...) OR U(...)
• T EXCEPT U holds rows where T(...) AND NOT U(...)
• SELECT DISTINCT * FROM T holds rows where T(...)
• SELECT DISTINCTcolumns to keepFROM T holds rows where
  there EXISTS values for columns to drop where T(...)
• VALUES (C1, C2, ...)((v1,v2, ...), ...) holds rows where
  C1 = v1 AND C2 = v2 AND ... OR ...

还有:

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

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

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

So to query we find a way of phrasing the predicate for the rows that we want in natural language using base table predicates, then in shorthand using base table predicates, then in shorthand using aliases in column names except for output columns, then in SQL using base table names plus ON & WHERE conditions etc. If we need to mention a base table twice then we give it aliases.

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

OUTER JOIN would be used when a class doesn't always have both facilitators or something doesn't always have all names. (Ie if a column can be NULL.) But you haven't given the specific predicates for your base table and query or the business rules about when things might be NULL so I have assumed no NULLs.

是否有根据人类可读的描述构造 SQL 查询的经验法则?

(重新 MS Access JOIN 括号见 this from SO 和 来自 MS.)

(Re MS Access JOIN parentheses see this from SO and this from MS.)

这篇关于如何从另一个 SQL 表中获取两个不同列的匹配数据:内部联接和/或联合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！