要求在第三个表格中将两个表格与他们的FK结合在一起 [英] Required to join 2 tables with their FKs in a 3rd table

问题描述

所以基本上，我正在跟着一个教程问题，问我下面。我不太清楚如何连接两个不包含其他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结合在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！