MySQL嵌套连接

Question

我正在存储生态调查的数据。在每个采样点采集多个个体，分别进行种名、属名和科名鉴定。

数据库中的表如下:

1) tab_indiv:存储找到的每个个体的数据。每个个体喜欢在物种表(tab_indiv.ref_id_species，个体所属的物种)中只有一条记录，在站点表(tab_indiv.ref_id_site，个体采样的站点)中也只有一条记录。

2) tab_site:进行调查的所有站点的列表。键(唯一 ID)是 tab_site.id_site

3) tab_species:找到的所有物种的列表。键(唯一 ID)是 tab_species.id_species。通过 tab_species.ref_id_genus 仅链接到属表中的一条记录。

4) tab_genus:找到的所有属的列表。键(唯一 ID)是 tab_genus.id_genus。通过 tab_genus.ref_id_family 仅链接到 family 表中的一条记录

5) tab_family:找到的所有族的列表。键(唯一 ID)是 tab_family.id_family。

我想做的是列出每个站点中发现的个体，以及它们的物种名称、属和科。我希望这样的事情会奏效:

SELECT
    tab_indiv.ref_id_species AS 'Species Name',
    tab_species.id_species AS 'Species Name 2', -- Just to check if I got the joins ok
    tab_genus.id_genus AS 'Genus Name',
    tab_family.id_family AS 'Family Name'
    tab_site.id_site AS 'Site Num'
FROM (tab_site
    LEFT JOIN tab_indiv
        ON tab_site.id_site = tab_indiv.ref_id_site
    LEFT JOIN tab_species
        ON tab_indiv.ref_id_species = tab_species.id_species
    LEFT JOIN tab_genus
        ON tab_species.ref_id_genus = tab_genus.id_genus
    LEFT JOIN tab_family
        ON tab_genus.ref_id_family = tab_family.id_family);

... 但它不起作用。如果每个站点有多个家庭，个人列表就会重复，并且所有个人都与所有家庭组合在一起，尽管每个人只能属于一个家庭。当我添加第三个 LEFT JOIN 时出现问题。

理想情况下我会得到这样的东西

sp1   |   gen1   |   fam1   |   site1
sp2   |   gen1   |   fam1   |   site1   -- sp1 and sp2 belongs to gen1    
sp3   |   gen2   |   fam2   |   site1
sp4   |   gen3   |   fam2   |   site1   -- gen1 and gen2 belongs to fam2

相反，我得到的是

sp1   |   gen1   |   fam1   |   site1    -- ok!
sp2   |   gen1   |   fam1   |   site1    -- ok!
sp1   |   gen1   |   fam2   |   site1    -- notice that sp1 and gen1 does not belong to fam2
sp2   |   gen1   |   fam2   |   site1    -- notice that sp2 and gen1 does not belong to fam2
sp3   |   gen2   |   fam1   |   site1    -- notice that sp3 and gen2 does not belong to fam1
sp4   |   gen3   |   fam1   |   site1    -- notice that sp4 and gen3 does not belong to fam2
sp3   |   gen2   |   fam2   |   site1    -- ok!
sp4   |   gen3   |   fam2   |   site1    -- ok!

有什么想法吗？欢迎并感谢您的建议!

Answer 1

试试这个，你真的不需要所有的表而且 LEFT JOIN 也没有用:

SELECT
    tab_indiv.ref_id_species,
    tab_species.ref_id_genus,
    tab_genus.ref_id_family,
    tab_indiv.ref_id_site
FROM 
    tab_indiv
    JOIN tab_species ON tab_indiv.ref_id_species = tab_species.id_species
    JOIN tab_genus ON tab_species.ref_id_genus = tab_genus.id_genus