需要在第三张桌子中加入 2 张桌子及其 FK [英] Required to join 2 tables with their FKs in a 3rd table
问题描述
所以基本上我正在关注一个教程问题，该问题问我以下问题.我不太确定如何加入不包含其他 FK 的 2 个表，它们(即他们的两个 FK)位于第三个表中.我可以得到一些帮助/解释吗?
so basically I `m following a tutorial question which asks me the below. I am not too sure how to join 2 tables which do not contain the others FK, their (i.e. both of their FKs) are located in a 3rd table. Could I get some help/explanation?
我的答案
SELECT Forest.Fo_name, Species.Sp_name, Species.Sp_woodtype
FROM Forest
INNER JOIN Species
ON Tree.Tr_species=Tree.Tr_forest
WHERE Fo_loc='ARTIC'
ORDER BY Fo_name, Sp_name
对于在编码为ARTIC"的区域中发现的森林，请列出在其中发现的森林名称和树种名称以及树种木材类型.消除所有重复项并按森林名称和树种名称排序输出"
"For forests found in the regions coded as "ARTIC" list the forest name & species name and species wood type found therein. Eliminate any duplicates and order the output by forest name and species name"
物种表
+--------------+------------------+------+--------------------------------+
| Field | Type | Key | Glossary |
+--------------+------------------+------+--------------------------------+
| sp_name | C(10) | PK | Species Name |
| sp_woodtype | C(10) | | Wood Yielded by tree |
| sp_maxht | I | | Max. Height |
+--------------+------------------+------+--------------------------------+
森林表
+--------------+------------------+------+--------------------------------+
| Field | Type | Key | Glossary |
+--------------+------------------+------+--------------------------------+
| Fo_name | C(10) | PK | Forest Name |
| Fo_size | I | | Forest Area |
| Fo_loc | C(10) | | Geographical Area |
| Fo_comp | C(10) | | Forest Owner |
+--------------+------------------+------+--------------------------------+
树形表
+--------------+------------------+------+---------------------------------------------+
| Field | Type | Key | Glossary |
+--------------+------------------+------+---------------------------------------------+
| Tr_species | C(10) | FK | (FK of species.sp_name |
| Tr_forest | C(10) | FK | (FK of forest.fo_name |
| Tr_numb | I | PK | Sequence number |
| Tr_planted | Date | | Date of planting |
| Tr_loc | C(10) | | Forest quadrant |
| Tr_parent | I | FK | (FK of tree.numb) procreating tree reference|
+--------------+------------------+------+---------------------------------------------+
C(10) &我代表字符 (10) &分别为整数
C(10) & I stand for character (10) & Integer respectively
推荐答案
连接表不需要外键！
因此，当它们之间没有 FK 时如何连接表的答案是只需加入它们.
So the answer to how to join tables when no FK is between them is to just join them.
真正的问题是我们如何选择加入哪些表(或以任何其他方式组合)?
声明和表格
每个基表都带有一个谓词——由列名参数化的语句模板.表值是使其谓词成为真正的命题--语句的行.
Every base table comes with a predicate--statement template parameterized by column names. The table value is the rows that make its predicate into a true proposition--statement.
// species [name] yields [woodtype] and has max height [maxht]
Species(name,woodtype,maxht)
// forest [name] has area [size] in area [loc] and owner [comp]
Forest(name,size,loc,comp)
// tree group [numb] is of species [species] in forest [forest] and was planted in [planted] in quadrant [loc] on date [date] with parent tree group [parent]
Tree(species,forest,numb,planted,loc,parent)
查询也有谓词.它的值也是使其谓词为真的行.它的谓词是根据它的FROM、WHERE等子句建立起来的.表别名将表值命名为与其基表类似，但列以别名为前缀.所以它的谓词是它的基表使用别名前缀列的谓词.
A query also has a predicate. Its value also is the rows that make its predicate true. Its predicate is built up according to its FROM, WHERE and other clauses. A table alias names a table value like its base table but with columns prefixed by the alias. So its predicate is its base table's predicate using alias-prefixed columns.
Species s
保持满足的行
species [s.name] yields [s.woodtype] and has max height [s.maxht]
交叉&INNER JOIN 在谓词之间放置 AND；UNION 在它们之间放置 OR；除了插入 AND NOT 和 ON &在哪里和在一个条件下；SELECT 重命名，添加 &删除列.(等其他运营商.)所以
CROSS & INNER JOIN put AND between predicates; UNION puts OR between them; EXCEPT inserts AND NOT and ON & WHERE AND in a condition; SELECT renames, adds & drops columns. (Etc for other operators.) So
Species s CROSS JOIN Forest f
保存行
species [s.name] yields [s.woodtype] and has max height [s.maxht]
AND forest [f.name] has area [f.size] in area [f.loc] and owner [f.comp]
(无论约束是什么！)如果您只希望上面的行具有以其木材类型命名的森林，那么您只需通过 ... WHERE f.name=s.woodtype 添加一个条件 因为这使值成为满足 ... AND f.name=s.woodtype 的行.
(No matter what the constraints are!) If you wanted only the rows above having forests named after their wood type then you would just add a condition via ... WHERE f.name=s.woodtype because that makes the value be rows satisfying ... AND f.name=s.woodtype.
对于在编码为北极"的地区发现的森林，请列出森林名称和在其中发现的物种名称和物种木材类型.消除所有重复项并按森林名称和物种名称对输出进行排序.
For forests found in the regions coded as "ARCTIC" list the forest name & species name and species wood type found therein. Eliminate any duplicates and order the output by forest name and species name.
这是返回的行要满足的一个很大的非正式谓词.如果我们尝试仅使用给定的谓词加上 AND、OR、AND NOT(等)来重写它，那么我们只能通过对所有三个给定的谓词进行 AND 来实现(因此，JOIN 基表名称)并添加 AND Forest.loc='ARCTIC'(因此，ON 或 WHERE条件).
That is a big informal predicate that the rows returned are to satisfy. If we try to rewrite it using only the predicates that we have been given plus AND, OR, AND NOT (etc) then we can only do it by ANDing all three given predicates (hence, JOIN of the base table names) and adding AND Forest.loc='ARCTIC' (hence, ON or WHERE that condition).
FK(等)和查询(非)
PK 和 FK 是完整性约束的特殊情况.给定谓词和可能出现的情况，只能出现一些数据库值.这就是完整性约束所描述的.他们让 DBMS 将不应该出现的数据库值排除在外.(另外，优化查询执行.)因为名称在 Species 中是唯一的，所以我们将其声明为键.森林名称和树麻木同上.因为 Tree 中的物种是 Species 中的名称，而名称是 Species 的键，所以我们声明 FK Tree.species->Species.name.森林和父母同上.与启用联接无关.(尽管它们暗示查询结果也满足某些约束.)
PKs and FKs are special cases of integrity constraints. Given the predicates and what situations can arise, only some database values can arise. That's what integrity constraints describe. They let the DBMS keep out database values that shouldn't arise. (Also, optimize query execution.) Because a name is unique in Species we declare it a key. Ditto for Forest name and Tree numb. Because a species in Tree is a name in Species and name is a key of Species we declare FK Tree.species->Species.name. Ditto for forest and parent. Nothing to do with enabling joins. (Although they imply that a query result satisfies certain constraints, too.)
查询约束是什么并不重要.如果存在由于业务规则或树或物种谓词不同而未显示为任何物种名称值的树物种值，则将没有 FK Tree.species->Species.name.但是每个查询将继续返回满足其谓词以基表谓词表示的行.(由于可能的业务情况或谓词不同，输入行可能不同，因此输出行也可能不同.)
It does not matter to querying what the constraints are. If there were Tree species values that did not appear as any Species name value because the business rules or Tree or Species predicate(s) were different then there would be no FK Tree.species->Species.name. But each query would continue to return the rows satisfying its predicate as expressed in terms of base table predicates. (Since the possible business situations or predicate(s) would be different, the input rows could be different so output rows could be different.)
什么决定了查询 SQL
因此，我们如何选择要连接(或以任何其他方式组合)哪些表的答案是，我们适当地安排基表名称、JOIN、UNION、EXCEPT 和 WHERE(等)，以给出谓词为的查询表达式我们希望我们的行满足的那个.这通常被认为是非正式的，可以通过感觉来完成，但现在您知道 SQL 与自然语言的联系了.并且约束是无关紧要的.
So the answer to how we choose what tables to join (or combine in any other way) is that we arrange base table names, JOIN, UNION, EXCEPT and WHERE (etc) as appropriate to give a query expression whose predicate is the one we want our rows to satisfy. This is usually taught as something informal to be done by feel but now you know what ties the SQL to the natural language. And constraints are irrelevant.
注意:前面假设我们没有从查询中返回重复项.之所以在关系模型中表中没有重复，是因为上面的表操作符和逻辑连接符的对应关系成立.但是 SQL 表可以有重复项.SQL 不同于关系模型的地方(在很多方面)，那里的查询变得不那么合乎逻辑.
NOTE: The preceding assumes that we return no duplicates from queries. The reason why there are no duplicates in tables in the relational model is so that the above correspondence between table operators and logic connectives holds. However SQL tables can have duplicates. Where SQL differs from the relational model (in its many ways), there querying becomes less (literally) logical.
重新加入以下外键
重新选择表格
是否有任何经验法则可以根据人类可读的描述构造 SQL 查询?
这篇关于需要在第三张桌子中加入 2 张桌子及其 FK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
