CODEWITHSUNDEEP
mysqlsqljoinrelational-database
Thiago Rangel
Thiago Rangel

Reputation: 85

MySQL nested join

I am storing data of a ecological survey. In each sampling site, multiple individuals are collected and identified for species name, genus name and family name.

The tables in the database are the following:

1) tab_indiv: stores data on each individual found. Each individual liks to only one record in the table of species (tab_indiv.ref_id_species, the species that individual belongs to) and only one record in the table of sites (tab_indiv.ref_id_site, the site where the individual was sampled from).

2) tab_site: list of all sites where the survey took place. The key (unique id) is tab_site.id_site

3) tab_species: list of all species found. The key (unique id) is tab_species.id_species. Links to only one record in the table of genus by tab_species.ref_id_genus.

4) tab_genus: list of all genus found. The key (unique id) is tab_genus.id_genus. Links to only one record in the table of family by tab_genus.ref_id_family

5) tab_family: list of all family found. The key (unique id) is tab_family.id_family.

What I want to do is list individuals found in each site, plust their species name, genus and family. I was hoping something like this would work:

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);

... but it does not work. If there are more than one family per site the list of individuals become duplicated, and all individuals are combined with all families, although each individual can only belong to a single family. The problem emerge when I add the third LEFT JOIN.

Ideally I would get something like this

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

instead, what I am getting is

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!

Any ideas? Your suggestion is welcome and appreciated!

Upvotes: 5

Views: 13807

Answers (1)

sashkello
sashkello

Reputation: 17871

Try this, you don't really need all the tables and LEFT JOIN is not useful as well:

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

Upvotes: 3

Related Questions