要求在第三个表格中将两个表格与他们的FK结合在一起

要求在第三个表格中将两个表格与他们的FK结合在一起

本文介绍了要求在第三个表格中将两个表格与他们的FK结合在一起的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

所以基本上,我正在跟着一个教程问题,问我下面。我不太清楚如何连接两个不包含其他FK的表,它们(即它们的FK)都位于第三个表中。
我可以得到一些帮助/解释吗?

我的答案

  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的地区列出了森林名称和物种名称以及在其中发现的物种木材类型。消除任何重复并按森林名称和物种名称命令输出



物种表

  + -------------- + ------- ----------- ------ + + ------------------------------- -  + 
|字段|类型|密钥|词汇表|
+ -------------- + ------------------ + ------ + ---- ---------------------------- +
| sp_name | C(10)| PK |物种名称|
| sp_woodtype | C(10)| |木由树|
| sp_maxht |我| |最大。高度|
+ -------------- + ------------------ + ------ + ---- ---------------------------- +

林表

  + -------------- + ------------------ + ------ + ------------------------ -------- + 
|字段|类型|密钥|词汇表|
+ -------------- + ------------------ + ------ + ---- ---------------------------- +
| Fo_name | C(10)| PK |森林名称|
| Fo_size |我| |森林地区|
| Fo_loc | C(10)| |地理区域|
| Fo_comp | C(10)| |森林所有者|
+ -------------- + ------------------ + ------ + ---- ---------------------------- +

树表

  + -------------- + ------------------ + ------ + ------------------------ --------------------- + 
|字段|类型|密钥|词汇表|
+ -------------- + ------------------ + ------ + ---- ----------------------------------------- +
| Tr_species | C(10)| FK | (FK of species.sp_name |
| Tr_forest | C(10)| FK |(forest.fo_name的FK |
| Tr_numb | I | PK |序列号|
| Tr_planted | Date | | |种植日期|
| Tr_loc | C(10)| |森林象限|
| Tr_parent | I | FK |(tree.numb的FK)生成树引用|
+ ------------- + ------------------ + ------ + ---------- ----------------------------------- +

解决方案

 <$ c 

你可以做多个连接,将树表链接到主表森林,然后链接物种表。 $ c> SELECT
Forest.Fo_name,
Species.Sp_name,
Species.Sp_woodtype

FROM
Fores t
INNER JOIN Tree ON Tree.Tr_forest = Forest.Fo_name
INNER JOIN种类ON Tree.Tr_species = Species.sp_name
$ b $ WHERE
Fo_loc ='ARTIC'

ORDER BY Fo_name,Sp_name


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?

My Answer

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

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

Species table

+--------------+------------------+------+--------------------------------+
| Field        | Type             |  Key | Glossary                       |
+--------------+------------------+------+--------------------------------+
| sp_name      | C(10)            |  PK  | Species Name                   |
| sp_woodtype  | C(10)            |      | Wood Yielded by tree           |
| sp_maxht     |  I               |      | Max. Height                    |
+--------------+------------------+------+--------------------------------+

Forest table

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

Tree table

+--------------+------------------+------+---------------------------------------------+
| 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) & I stand for character (10) & Integer respectively

解决方案

You could do multiple joins. Link the tree table to your main table forest, then link the species table:

SELECT
Forest.Fo_name,
Species.Sp_name,
Species.Sp_woodtype

FROM
Forest
INNER JOIN Tree ON Tree.Tr_forest=Forest.Fo_name
INNER JOIN Species ON Tree.Tr_species = Species.sp_name

WHERE
Fo_loc='ARTIC'

ORDER BY Fo_name, Sp_name

这篇关于要求在第三个表格中将两个表格与他们的FK结合在一起的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-06 20:13