[select]
countUnits=SELECT GROUP_CONCAT(distinct occurrenceid) from rawoccurrence
taxon=SELECT  group_concat(distinct canonicalname order by preferred desc),GROUP_CONCAT(distinct fk_occurrenceid), COUNT(distinct fk_occurrenceid)  
countRecordBasis=SELECT  GROUP_CONCAT(distinct occurrenceid),recordBasis  
country=SELECT distinct country
collector=SELECT collectornames 
institution = SELECT DISTINCT  institutioncode 
collection = SELECT DISTINCT collectioncode
full = SELECT  group_concat(distinct canonicalname order by preferred desc),group_concat(distinct genusOrMonomial order by preferred desc),h1.highertaxon as family,concat(h2.higherrank,':',h2.highertaxon) as higher,'common',country,locality,collectioncode,institutioncode,unitid,gatheringdate,group_concat(url),occurrenceid,longitude,latitude,replace(concat('(',IFNULL(t.bracketauthorship,''),')',IFNULL(t.author,'')),'()',''),recordBasis,collectornames,collectorNumber,fieldNumber,fk_datasourceid
occurrenceid = SELECT  DISTINCT occurrenceid 
countoccurrenceid=SELECT COUNT(distinct occurrenceid) 
nbMulti =  SELECT count(multimediaid) FROM multimedia m JOIN tripleidstore on tripleidstoreid=m.fk_tripleidstoreid JOIN rawoccurrence r ON tripleidstoreid=r.fk_tripleidstoreid
higherTaxon = SELECT distinct highertaxon
genus = SELECT distinct group_concat(distinct genusOrMonomial order by preferred desc) 
family = SELECT distinct highertaxon
recordBasis = SELECT distinct GROUP_CONCAT(distinct occurrenceid), recordbasis
unitsDetails =  SELECT unitID, collectionID, institutionID FROM tripleidstoreid JOIN rawoccurrence ON fk_tripleidstoreid=tripleidstoreid
getRecordBasis = SELECT distinct recordbasis

[from]
taxon= FROM identification 
bioRecord =  FROM rawoccurrence JOIN tripleidstore on fk_tripleidstoreid=tripleidstoreid JOIN identification on fk_occurrenceid=occurrenceid 
occurrence =  FROM rawoccurrence JOIN tripleidstore on fk_tripleidstoreid=tripleidstoreid JOIN identification on fk_occurrenceid=occurrenceid 
countRecordBasis = FROM rawoccurrence JOIN tripleidstore on fk_tripleidstoreid=tripleidstoreid JOIN recordbasis ON recordbasisid = fk_recordbasisid
country= FROM rawoccurrence
collector = FROM collectors JOIN rawoccurrence ON fk_occurrenceid=occurrenceid  
institution =  FROM tripleidstore  JOIN rawoccurrence ON fk_tripleidstoreid=tripleidstoreid 
collection = FROM tripleidstore   JOIN rawoccurrence ON fk_tripleidstoreid=tripleidstoreid 
full =  FROM rawoccurrence  JOIN identification i ON fk_occurrenceid=occurrenceid JOIN tripleidstore on fk_tripleidstoreid=tripleidstoreid  JOIN recordbasis ON fk_recordbasisid=recordbasisid LEFT JOIN coordinates c ON c.fk_occurrenceid=occurrenceid  LEFT JOIN collectors cv ON cv.fk_occurrenceid=occurrenceid LEFT JOIN multimedia m ON m.fk_tripleidstoreid=tripleidstoreid LEFT JOIN identificationtohigher ON fk_rawidentificationid=identificationid LEFT JOIN higher h1 ON h1.highertaxaid=fk_highertaxaid AND h1.higherrank like 'fami%' LEFT JOIN higher h2 ON h2.highertaxaid=fk_highertaxaid AND h2.higherrank not like 'fami%' LEFT JOIN taxonAtomised t on i.fullscientificname=t.fullscientificname    
higherTaxon = FROM higher JOIN identificationtohigher ON fk_highertaxaid=highertaxaid JOIN identification ON identificationid=fk_rawidentificationid  JOIN rawoccurrence ON fk_occurrenceid=occurrenceid
genus= FROM identification  JOIN rawoccurrence ON fk_occurrenceid=occurrenceid 
family = FROM higher JOIN identificationtohigher ON fk_highertaxaid=highertaxaid JOIN identification on fk_rawidentificationid=identificationid 
recordBasis= FROM recordbasis JOIN rawoccurrence on fk_recordbasisid=recordbasisid 
getRecordBasis = FROM recordbasis
 
[where]
taxon= WHERE canonicalname 
taxonid = WHERE occurrenceid 
countRecordBasis = WHERE occurrenceid 
country= WHERE occurrenceid 
collector= WHERE fk_occurrenceid 
institution = WHERE occurrenceid
collection  = WHERE occurrenceid
occurrenceid = WHERE occurrenceid  
imagerecordbiorecordkey = WHERE occurrenceid 
higherTaxon = WHERE fk_occurrenceid 
genus= WHERE fk_occurrenceid 
family= WHERE higherrank like 'famil%' and fk_occurrenceid 
recordBasis = WHERE occurrenceid 
locality= WHERE locality 
 
[or]
taxon= OR canonicalname 
taxonName= OR canonicalname 
 
[and]
collection= AND collectioncode
institution= AND institutioncode
occurrenceid= AND rawoccurrenceid
#new occurrenceid TO DO test

[groupBy]
countRecordBasis = GROUP BY recordbasis
#taxon = GROUP BY canonicalname
recordBasis = GROUP BY recordbasis
taxon = GROUP BY occurrenceid
taxonSimple = GROUP BY fk_occurrenceid
full = GROUP BY occurrenceid ,tripleidstoreid


[mysql] 
not = not         
and = and 
or = or 
isnull = is null 
isnotnull = is not null 
equals = = 
notequals = <> 
like = LIKE 
lessthan = < 
lessthanorequals = <= 
greaterthan = > 
greaterthanorequals = >= 
in = IN 
 
[escape]     
'= ' 
* = %