
#create a temp table with all taxonKeys that have a higher taxon associated denoting a plant
create temporary table plantaeHigherTaxa (
	taxonKey int primary key);

insert into plantaeHigherTaxa
select distinct taxonKey
from syn_delta.higherTaxon
where higherTaxonName like '%phyceae';

# german plantae records
create table mergeList
select straight_join bioRecordKey as brKey
from syn_delta.geoRegion
join syn_delta.geoRegionRecord on geoRegionKey = geoRegionRecordRegionKey
join syn_delta.bioRecord       on bioRecordKey = geoRegionRecordBioRecordKey
left join syn_delta.scientific		   on scitaxonkey  = bioRecordTaxonkey
left join plantaeHigherTaxa 				on taxonKey = bioRecordTaxonKey
where (geoRegion.geoRegionname regexp 'deutsch|germany|german |brd|ddr|bavaria|bayern|bayerisch|berlin|brandenburg|bremen|baden|hamburg|hesse|mecklenburg-vorpomm|pomerania|sachsen|saxony|nrw|nordrhein|westfal|westphal|rhine|rhein|pfalz|saarland|schleswig|holstein|thuring'
   		 or geoRegion.geoRegionname in ('bay')
	 	   or geoRegion.isoCountryCode = 'DE')
  and (scikingdom = 'Plantae' or plantaeHigherTaxa.taxonKey is not null)
		 /* so either kingdom "plantae" or another higher taxon that denotes a plant*/

  union

#BfN databases
select bioRecordKey from syn_delta.bioRecord
where bioRecordResourceKey  in (910, 1407);

alter table mergeList
	add key (brKey);

# remove bioRecords that have a CountryCode <> DE
delete from mergeList
using mergeList
join syn_delta.bioRecord on bioRecordKey = brKey
where bioRecordCountryCode is not null
  and bioRecordCountryCode <> 'DE';
