1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35

 WITH cte
      AS (SELECT hist,
                 curr,
                 Coalesce(c.species, h.species) AS code
          FROM   (SELECT block,
                         species,
                         breeding_code AS hist
                  FROM   historic
                  WHERE  {$where}) AS h
                 FULL OUTER JOIN
                 (SELECT block,
                         species,
                         breeding_code AS curr
                  FROM   current
                  WHERE  {$where}) AS c
               ON h.species = c.species)
 
 SELECT commonname AS "Common Name",
        code AS "Code",
        CASE
          WHEN hist = 4 THEN 'Confirmed'
          WHEN hist = 3 THEN 'Probable'
          WHEN hist = 2 THEN 'Possible'
          ELSE '---'
        END AS "Shuford",
        CASE
          WHEN curr = 4 THEN 'Confirmed'
          WHEN curr = 3 THEN 'Probable'
          WHEN curr = 2 THEN 'Possible'
          ELSE '---'
        END AS "Current"
 FROM   cte
        LEFT JOIN species
               ON code = spec
 ORDER  BY id