title: MySQL nested join
body: <p>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.</p>
  
  <p>The tables in the database are the following:</p>
  
  <p>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).</p>
  
  <p>2) tab_site: list of all sites where the survey took place. The key (unique id) is tab_site.id_site</p>
  
  <p>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.</p>
  
  <p>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</p>
  
  <p>5) tab_family: list of all family found. The key (unique id) is tab_family.id_family.</p>
  
  <p>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:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>... 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.</p>
  
  <p>Ideally I would get something like this</p>
  
  <pre><code>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
  </code></pre>
  
  <p>instead, what I am getting is</p>
  
  <pre><code>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!
  </code></pre>
  
  <p>Any ideas? Your suggestion is welcome and appreciated!</p>