Misc queries

Mash up plant concepts (reference and names) with alt usages, here truncating the name strings just for illustration, and restricting the USDA 2015 (first 20 records):

select pc.plantconcept_id,
       r.shortname as reference,
       left(pn.plantname, 20) as name,
       pu_co.plantname as code,
       pu_cn.plantname as common_name,
       left(pu_sci.plantname, 20) as sci_name,
       left(pu_sca.plantname, 20) as sca_name
  from plantconcept pc join reference r using (reference_id)
  join plantname pn using (plantname_id)
  left join (select plantconcept_id, plantname
               from plantusage
               where lower(classsystem) = 'code') pu_co
            on pc.plantconcept_id = pu_co.plantconcept_id
  left join (select plantconcept_id, plantname
               from plantusage
               where lower(classsystem) = 'english common') pu_cn
            on pc.plantconcept_id = pu_cn.plantconcept_id
  left join (select plantconcept_id, plantname
               from plantusage
               where lower(classsystem) = 'scientific without authors') pu_sci
            on pc.plantconcept_id = pu_sci.plantconcept_id
  left join (select plantconcept_id, plantname
               from plantusage
               where lower(classsystem) = 'scientific') pu_sca
            on pc.plantconcept_id = pu_sca.plantconcept_id
  where pc.reference_id = 50940
  limit 20;
--  plantconcept_id |    reference     |         name         | code  |    common_name     |       sci_name       |       sca_name
-- -----------------+------------------+----------------------+-------+--------------------+----------------------+----------------------
--           295580 | USDA Plants 2015 | Abelia               | ABELI | Abelia             | Abelia               | Abelia R. Br.
--           295581 | USDA Plants 2015 | Abelia ×grandiflora  | ABGR4 | glossy abelia      | Abelia ×grandiflora  | Abelia ×grandiflora
--           295582 | USDA Plants 2015 | Abelmoschus          | ABELM | okra               | Abelmoschus          | Abelmoschus Medik.
--           295583 | USDA Plants 2015 | Abelmoschus esculent | ABES  | okra               | Abelmoschus esculent | Abelmoschus esculent
--           295584 | USDA Plants 2015 | Hibiscus esculentus  | HIES  |                    | Hibiscus esculentus  | Hibiscus esculentus
--           295585 | USDA Plants 2015 | Abelmoschus manihot  | ABMA9 |                    | Abelmoschus manihot  | Abelmoschus manihot
--           295586 | USDA Plants 2015 | Hibiscus manihot     | HIMA9 |                    | Hibiscus manihot     | Hibiscus manihot L.
--           295587 | USDA Plants 2015 | Abelmoschus moschatu | ABMO  | musk okra          | Abelmoschus moschatu | Abelmoschus moschatu
--           295588 | USDA Plants 2015 | Hibiscus abelmoschus | HIAB  |                    | Hibiscus abelmoschus | Hibiscus abelmoschus
--           295589 | USDA Plants 2015 | Abies                | ABIES | fir                | Abies                | Abies Mill.
--           295590 | USDA Plants 2015 | Abies alba           | ABAL3 | silver fir         | Abies alba           | Abies alba Mill.
--           295591 | USDA Plants 2015 | Abies amabilis       | ABAM  | Pacific silver fir | Abies amabilis       | Abies amabilis (Doug
--           295592 | USDA Plants 2015 | Abies balsamea var.  | ABBAB | balsam fir         | Abies balsamea var.  | Abies balsamea (L.)
--           295593 | USDA Plants 2015 | Pinus balsamea       | PIBA3 |                    | Pinus balsamea       | Pinus balsamea L.
--           295594 | USDA Plants 2015 | Abies balsamea var.  | ABBAP |                    | Abies balsamea var.  | Abies balsamea (L.)
--           295595 | USDA Plants 2015 | Abies ×phanerolepis  | ABPH  |                    | Abies ×phanerolepis  | Abies ×phanerolepis
--           295596 | USDA Plants 2015 | Abies balsamea       | ABBA  | balsam fir         | Abies balsamea       | Abies balsamea (L.)
--           295597 | USDA Plants 2015 | Abies bracteata      | ABBR  | bristlecone fir    | Abies bracteata      | Abies bracteata (D.
--           295598 | USDA Plants 2015 | Abies venusta        | ABVE  |                    | Abies venusta        | Abies venusta (Dougl
--           295599 | USDA Plants 2015 | Abies concolor var.  | ABCOC | white fir          | Abies concolor var.  | Abies concolor (Gord