SELECT o.data_provider_id AS 'data_provider_id', o.data_resource_id AS 'data_resource_id', dp.name AS 'Data_provider', dr.name AS 'Dataset', replace(IFNULL(r.collector_name, ""), "\n", " ") AS 'Collector_name', '' AS 'Collector_number', '' AS 'Field_number', '' AS 'GUID', o.occurrence_date AS 'Date_collected', r.institution_code AS 'Institution_code', r.collection_code AS 'Collection_code', r.catalogue_number AS 'Catalogue_number', lub.br_value AS 'Basis_of_record', ir.url AS 'Image_url', o.modified AS 'last_indexed', r.identifier_name AS 'Identifier', r.identification_date AS 'Identification_date', replace(IFNULL(r.scientific_name, ""), "\n", " ") AS 'Scientific_name', replace(IFNULL(r.author, ""), "\n", " ") AS 'Author', nn.canonical AS 'Scientific_name_interpreted', r.kingdom AS 'Kingdom_original', kn.canonical AS 'Kingdom', r.phylum AS 'Phylum_original', pn.canonical AS 'Phylum', r.class AS 'Class_original', cn.canonical AS 'Class', r.order_rank AS 'Order_rank_original', orn.canonical AS 'Order_rank', r.family AS 'Family_original', fn.canonical AS 'Family', r.genus AS 'Genus_original', gn.canonical AS 'Genus', replace(IFNULL(r.country, ""), "\n", " ") AS 'Country', replace(IFNULL(o.iso_country_code, ""), "\n", " ") AS 'Country_interpreted', replace(IFNULL(r.locality, ""), "\n", " ") AS 'Locality', r.county AS 'County', r.continent_ocean AS 'Continent_or_Ocean', r.state_province AS 'State_Province', c.region AS 'Region', dp.iso_country_code AS 'Provider_country', r.latitude AS 'Latitude', o.latitude AS 'Latitude_interpreted', r.longitude AS 'Longitude', o.longitude AS 'Longitude_interpreted', r.lat_long_precision AS 'Coordinate_precision', o.geospatial_issue AS 'geospatial_issue', o.cell_id AS 'cell_id', o.centi_cell_id AS 'centi_cell_id', r.min_depth AS 'Min_depth', r.max_depth AS 'Max_depth', r.min_altitude AS 'Min_altitude', r.max_altitude AS 'Max_altitude', r.altitude_precision AS 'Altitude_precision', concat('http://data.gbif.org/occurrences/',o.id) AS 'gbif_portal_url', concat('http://data.gbif.org/ws/rest/occurrence/get/',o.id) AS 'gbif_webservice_url', o.id AS 'occurrence_id' , o.nub_concept_id AS 'nub_concept_id' FROM occurrence_record o JOIN data_resource dr ON dr.id = o.data_resource_id JOIN data_provider dp ON o.data_provider_id = dp.id LEFT JOIN country c ON o.iso_country_code = c.iso_country_code LEFT JOIN lookup_basis_of_record lub ON o.basis_of_record=lub.br_key LEFT JOIN image_record ir ON o.id = ir.occurrence_id INNER JOIN raw_occurrence_record r ON o.id=r.id INNER JOIN taxon_concept nc ON o.nub_concept_id=nc.id INNER JOIN taxon_name nn ON nc.taxon_name_id=nn.id LEFT JOIN taxon_concept kc ON nc.kingdom_concept_id=kc.id LEFT JOIN taxon_name kn ON kc.taxon_name_id=kn.id LEFT JOIN taxon_concept pc ON nc.phylum_concept_id=pc.id LEFT JOIN taxon_name pn ON pc.taxon_name_id=pn.id LEFT JOIN taxon_concept cc ON nc.class_concept_id=cc.id LEFT JOIN taxon_name cn ON cc.taxon_name_id=cn.id LEFT JOIN taxon_concept oc ON nc.order_concept_id=oc.id LEFT JOIN taxon_name orn ON oc.taxon_name_id=orn.id LEFT JOIN taxon_concept fc ON nc.family_concept_id=fc.id LEFT JOIN taxon_name fn ON fc.taxon_name_id=fn.id LEFT JOIN taxon_concept gc ON nc.genus_concept_id=gc.id LEFT JOIN taxon_name gn ON gc.taxon_name_id=gn.id WHERE o.iso_country_code = 'GB' AND o.latitude IS NOT NULL AND o.longitude IS NOT NULL LIMIT 500 INTO OUTFILE '/var/lib/mysql/Andrew_UK_20120523_sample.txt' ;