如何从另一个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.

这是我工作中的内部加入:

This is my working Inner Join:

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.

推荐答案

每个基表都有一个语句模板(又称为 predicate )，该模板由列名进行参数化，我们将其放入一行或保留一行出去.我们可以为谓词使用简写，就像它的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 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包含(分别为T(...)和U(...)的(无NULL的非重复)行，则:

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(...)和U(...)
的行
• T INNER JOIN U ON condition 保存的行中T(...)AND U(...)AND 条件
• T LEFT JOIN U ON condition 保存其中的行(对于仅U列U1，...)
      T(...)AND U(...)AND 条件
  或T(...)
     并且不存在U1的值，...其中[U(...)和 condition ]
      AND U1为NULL AND ...
• T WHERE condition 保存的行中T(...)和条件
• T INTERSECT U保留其中T(...)和U(...)
的行
• T UNION U保留其中T(...)或U(...)
的行
• T EXCEPT U保存的行中T(...)而不是U(...)
• SELECT DISTINCT * FROM T保留其中T(...)
的行
• SELECT DISTINCT columns to keep FROM T保存其中的行，其中
  存在个要删除的列的EXISTS值，其中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(标量)
• 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中使用基表名(必要时加上条件).如果我们需要两次提及一个表，那么我们给它取别名.

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 SQL using base table names (plus conditions wherever needed). If we need to mention a 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

// table names & (MS Access) SQL
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.

(有关MS Access JOIN括号的信息，请参见此摘自SO 和

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

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