SELECT datasource.organizationname AS `dataSourceName`, family.plantname AS family, genus.plantname AS genus, species.plantname AS species, binomial.plantname AS taxon, authority.plantname AS `taxonAuthor`, variety.plantname AS `taxonMorphospecies`, country.placename AS country, stateprovince.placename AS `stateProvince`, county.placename AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, location.area AS `plotAreaHa`, method.name AS `plotMethod`, locationdetermination.latitude, locationdetermination.longitude, location.elevation AS elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, aggregateoccurrence.cover AS `pctCover` FROM (((((((((((((((((((((((((((((((((((((((((location LEFT JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationdetermination USING (location_id)) LEFT JOIN namedplace_ancestor continent_ancestor ON ((continent_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace continent ON (((continent.namedplace_id = continent_ancestor.ancestor_id) AND (continent.rank = CAST('continent' AS placerank))))) LEFT JOIN namedplace_ancestor country_ancestor ON ((country_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace country ON (((country.namedplace_id = country_ancestor.ancestor_id) AND (country.rank = CAST('country' AS placerank))))) LEFT JOIN namedplace_ancestor stateprovince_ancestor ON ((stateprovince_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace stateprovince ON (((stateprovince.namedplace_id = stateprovince_ancestor.ancestor_id) AND (stateprovince.rank = CAST('stateProvince' AS placerank))))) LEFT JOIN namedplace_ancestor county_ancestor ON ((county_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace county ON (((county.namedplace_id = county_ancestor.ancestor_id) AND (county.rank = CAST('county' AS placerank))))) LEFT JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) LEFT JOIN plantconcept USING (plantconcept_id)) LEFT JOIN plantname_ancestor binomial_ancestor ON ((binomial_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname binomial ON (((binomial.plantname_id = binomial_ancestor.ancestor_id) AND (binomial.rank = CAST('binomial' AS taxonrank))))) LEFT JOIN plantname_ancestor kingdom_ancestor ON ((kingdom_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname kingdom ON (((kingdom.plantname_id = kingdom_ancestor.ancestor_id) AND (kingdom.rank = CAST('kingdom' AS taxonrank))))) LEFT JOIN plantname_ancestor division_ancestor ON ((division_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname division ON (((division.plantname_id = division_ancestor.ancestor_id) AND (division.rank = CAST('division' AS taxonrank))))) LEFT JOIN plantname_ancestor class_ancestor ON ((class_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname class ON (((class.plantname_id = class_ancestor.ancestor_id) AND (class.rank = CAST('class' AS taxonrank))))) LEFT JOIN plantname_ancestor order_ancestor ON ((order_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname `order` ON (((`order`.plantname_id = order_ancestor.ancestor_id) AND (`order`.rank = CAST('order' AS taxonrank))))) LEFT JOIN plantname_ancestor family_ancestor ON ((family_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname family ON (((family.plantname_id = family_ancestor.ancestor_id) AND (family.rank = CAST('family' AS taxonrank))))) LEFT JOIN plantname_ancestor genus_ancestor ON ((genus_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname genus ON (((genus.plantname_id = genus_ancestor.ancestor_id) AND (genus.rank = CAST('genus' AS taxonrank))))) LEFT JOIN plantname_ancestor species_ancestor ON ((species_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname species ON (((species.plantname_id = species_ancestor.ancestor_id) AND (species.rank = CAST('species' AS taxonrank))))) LEFT JOIN plantname_ancestor subspecies_ancestor ON ((subspecies_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname subspecies ON (((subspecies.plantname_id = subspecies_ancestor.ancestor_id) AND (subspecies.rank = CAST('subspecies' AS taxonrank))))) LEFT JOIN plantname_ancestor authority_ancestor ON ((authority_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname authority ON (((authority.plantname_id = authority_ancestor.ancestor_id) AND (authority.rank = CAST('authority' AS taxonrank))))) LEFT JOIN plantname_ancestor variety_ancestor ON ((variety_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname variety ON (((variety.plantname_id = variety_ancestor.ancestor_id) AND (variety.rank = CAST('variety' AS taxonrank))))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

    SELECT datasource.organizationname AS "dataSourceName", family.plantname AS family, genus.plantname AS genus, species.plantname AS species, binomial.plantname AS taxon, authority.plantname AS "taxonAuthor", variety.plantname AS "taxonMorphospecies", country.placename AS country, stateprovince.placename AS "stateProvince", county.placename AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", location.area AS "plotAreaHa", method.name AS "plotMethod", locationdetermination.latitude, locationdetermination.longitude, location.elevation AS elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", aggregateoccurrence.cover AS "pctCover" FROM (((((((((((((((((((((((((((((((((((((((((location LEFT JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationdetermination USING (location_id)) LEFT JOIN namedplace_ancestor continent_ancestor ON ((continent_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace continent ON (((continent.namedplace_id = continent_ancestor.ancestor_id) AND (continent.rank = 'continent'::placerank)))) LEFT JOIN namedplace_ancestor country_ancestor ON ((country_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace country ON (((country.namedplace_id = country_ancestor.ancestor_id) AND (country.rank = 'country'::placerank)))) LEFT JOIN namedplace_ancestor stateprovince_ancestor ON ((stateprovince_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace stateprovince ON (((stateprovince.namedplace_id = stateprovince_ancestor.ancestor_id) AND (stateprovince.rank = 'stateProvince'::placerank)))) LEFT JOIN namedplace_ancestor county_ancestor ON ((county_ancestor.namedplace_id = locationdetermination.namedplace_id))) LEFT JOIN namedplace county ON (((county.namedplace_id = county_ancestor.ancestor_id) AND (county.rank = 'county'::placerank)))) LEFT JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) LEFT JOIN plantconcept USING (plantconcept_id)) LEFT JOIN plantname_ancestor binomial_ancestor ON ((binomial_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname binomial ON (((binomial.plantname_id = binomial_ancestor.ancestor_id) AND (binomial.rank = 'binomial'::taxonrank)))) LEFT JOIN plantname_ancestor kingdom_ancestor ON ((kingdom_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname kingdom ON (((kingdom.plantname_id = kingdom_ancestor.ancestor_id) AND (kingdom.rank = 'kingdom'::taxonrank)))) LEFT JOIN plantname_ancestor division_ancestor ON ((division_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname division ON (((division.plantname_id = division_ancestor.ancestor_id) AND (division.rank = 'division'::taxonrank)))) LEFT JOIN plantname_ancestor class_ancestor ON ((class_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname class ON (((class.plantname_id = class_ancestor.ancestor_id) AND (class.rank = 'class'::taxonrank)))) LEFT JOIN plantname_ancestor order_ancestor ON ((order_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname "order" ON ((("order".plantname_id = order_ancestor.ancestor_id) AND ("order".rank = 'order'::taxonrank)))) LEFT JOIN plantname_ancestor family_ancestor ON ((family_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname family ON (((family.plantname_id = family_ancestor.ancestor_id) AND (family.rank = 'family'::taxonrank)))) LEFT JOIN plantname_ancestor genus_ancestor ON ((genus_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname genus ON (((genus.plantname_id = genus_ancestor.ancestor_id) AND (genus.rank = 'genus'::taxonrank)))) LEFT JOIN plantname_ancestor species_ancestor ON ((species_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname species ON (((species.plantname_id = species_ancestor.ancestor_id) AND (species.rank = 'species'::taxonrank)))) LEFT JOIN plantname_ancestor subspecies_ancestor ON ((subspecies_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname subspecies ON (((subspecies.plantname_id = subspecies_ancestor.ancestor_id) AND (subspecies.rank = 'subspecies'::taxonrank)))) LEFT JOIN plantname_ancestor authority_ancestor ON ((authority_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname authority ON (((authority.plantname_id = authority_ancestor.ancestor_id) AND (authority.rank = 'authority'::taxonrank)))) LEFT JOIN plantname_ancestor variety_ancestor ON ((variety_ancestor.plantname_id = plantconcept.plantname_id))) LEFT JOIN plantname variety ON (((variety.plantname_id = variety_ancestor.ancestor_id) AND (variety.rank = 'variety'::taxonrank)))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));