The outer join of a subquery of the most common plant species

Question

MySQL 5.5.43

I am working on a database of 7200 cannabis strains and should show a list of strains along with the most popular claimed species by their breeders.

The topic is pretty confusing, so here are a few facts to help you figure out where my trouble is:

• Each cannabis strain is one of the following species; Indica, Sativa or Ruderalis, or it may be a cross of all three.
• Popular strains can have up to 30 different breeders producing the seeds of this strain.
• Each breeder of this single strain may have slightly different crosses / genetics and report different species. For example: Breeder1 claims that StrainX is a 100% application of Indica and Breeder2. StrainX is primarily Indica (possibly 90% Indica and 10% Sativa). The influence of the Sativa plant seems to be uplifting, while Indica is a little depressing, so it is very important to record small differences in each breed for medicinal purposes.

EXAMPLES OF CHECKS:

For one very popular strain called White Widow, this is the result I created. It has 29 different breeders, with each breeder applying for different species. As can be seen from the results, the most popular type of this strain is Indica / Sativa (an equal hybrid).

SELECT
  s.id,
  b.id AS breederID,
  b.breederName AS breederName,
  GROUP_CONCAT(DISTINCT sp.species ORDER BY sp.species ASC SEPARATOR '/') AS species
FROM strains AS s
LEFT JOIN strainBreedersDir AS sbd ON s.id = sbd.strainID
LEFT JOIN breeders AS b ON sbd.breederID = b.id
LEFT JOIN strainBreederSpeciesDir AS sbsd ON s.id = sbsd.strainID AND sbd.breederID = sbsd.breederID
LEFT JOIN species AS sp ON sbsd.speciesID = sp.id
WHERE s.id = 6782
GROUP BY s.id, sbd.breederID

RESULT I WANT

, / . , , , , Indica/Sativa , :

strainID  | strainName      | breeders                 | averageSpecies
--------------------------------------------------------------------------
6782      | White Widow     | Green House Seeds,       | Indica/Sativa
          |                 | Barney Farm

:

, , . , , , 100 . , "", , , . , , . :

SELECT
  s.id,
  s.strainName,
  GROUP_CONCAT(DISTINCT b.breederName ORDER BY b.breederName ASC separator ', ') AS breeders,
  COALESCE(NULLIF(ps.primarySpecies,''),'Unknown') AS primarySpecies
FROM strains AS s
LEFT JOIN strainBreedersDir AS sbd ON s.id = sbd.strainID
LEFT JOIN breeders AS b ON sbd.breederID = b.id
LEFT OUTER JOIN (
  SELECT
    sbd.breederID AS breederID,
    GROUP_CONCAT(DISTINCT sp.species ORDER BY sp.species ASC SEPARATOR '/') AS primarySpecies
  FROM strains AS s
  LEFT JOIN strainBreedersDir AS sbd ON s.id = sbd.strainID
  LEFT JOIN strainBreederSpeciesDir AS sbsd ON s.id = sbsd.strainID AND sbd.breederID = sbsd.breederID
  LEFT JOIN species AS sp ON sbsd.speciesID = sp.id
  GROUP BY s.id, sbd.breederID
) AS ps ON sbd.breederID = ps.breederID
WHERE s.id = 6782
GROUP BY s.id

id   | strainName   | breeders           | species
----------------------------------------------------------
6782 | White Widow  | Green House Seeds, | Indica/Sativa
     |              | Barney Farm      |

, OUTER JOIN, , . , .

?

:

strains

id (PK AUTO)     |  strainName (UNIQUE)
---------------------------------------------
6782             |  White Widow

-

strainBreedersDir

strainID (FK UNIQUE)     | breederID (UNIQUE)
---------------------------------------------
6782                     | 16
6782                     | 23

-

breeders

id (PK AUTO)      | breederName (UNIQUE)
---------------------------------------------
16                | Green House Seeds
23                | Barney Farm

-

strainBreederSpeciesDir

strainID (FK UNIQUE)  | breederID (INT UNIQUE)  | speciesID (INT UNIQUE)
----------------------------------------------------------------------
6782                  | 16                      | 1
6782                  | 16                      | 2
6782                  | 23                      | 5

-

species

id (PK AUTO)  | species (UNIQUE)
-------------------------------------
1             | Indica
2             | Sativa
3             | Ruderalis
4             | Mostly Indica
5             | Mostly Sativa
6             | Mostly Ruderalis

- SQLFIDDLE - .

Answer 1

, .

Id, , -, , , . , GROUP_CONCAT , , ( - ). MAX AVG. MAX AVG.

SELECT MAX(aggregated.theCount),
aggregated.id,
aggregated.breederID,
aggregated.breeders as mostPopularBreeders,
aggregated.species as mostPopularSpecies,
AllStrainBreeders.allBreeders as strainBreeders
      FROM(
    SELECT 
        speciesWithBreeder.id,
        speciesWithBreeder.breederID,
        speciesWithBreeder.breederName,
        GROUP_CONCAT(DISTINCT speciesWithBreeder.breederName ORDER BY speciesWithBreeder.breederName ASC separator ', ') AS breeders,
        speciesWithBreeder.species,
        COUNT(*) as theCount
    FROM(
        SELECT
          s.id,
          b.id AS breederID,
          b.breederName AS breederName,
          GROUP_CONCAT(DISTINCT sp.species ORDER BY sp.species ASC SEPARATOR '/') AS species
        FROM strains AS s
        LEFT JOIN strainBreedersDir AS sbd ON s.id = sbd.strainID
        LEFT JOIN breeders AS b ON sbd.breederID = b.id
        LEFT JOIN strainBreederSpeciesDir AS sbsd ON s.id = sbsd.strainID AND sbd.breederID = sbsd.breederID
        INNER JOIN species AS sp ON sbsd.speciesID = sp.id
        WHERE s.id = 6782
        GROUP BY s.id, sbd.breederID) 
    AS speciesWithBreeder
GROUP BY speciesWithBreeder.species
ORDER BY COUNT(*) DESC
  ) as aggregated 
  LEFT JOIN(
    SELECT 
    sbd.strainID,
    GROUP_CONCAT(DISTINCT b.breederName ORDER BY b.breederName ASC SEPARATOR ',') AS allBreeders
    FROM breeders b  
    LEFT JOIN strainBreedersDir sbd ON sbd.breederID = b.id AND sbd.strainID = 6782
    GROUP BY sbd.strainID
   ) as AllStrainBreeders      
   ON aggregated.id = AllStrainBreeders.strainID
GROUP BY aggregated.id