[select] countUnits=SELECT count(distinct occurrenceid) from rawoccurrence taxon=SELECT cast( group_concat(distinct canonicalname order by preferred desc) as char),cast( GROUP_CONCAT(distinct fk_occurrenceid) as char), COUNT(distinct fk_occurrenceid) countRecordBasis=SELECT count(distinct occurrenceid),recordBasis country=SELECT distinct country collector=SELECT collectornames institution = SELECT DISTINCT institutioncode collection = SELECT DISTINCT collectioncode full = SELECT cast( group_concat(distinct canonicalname order by preferred desc) as char),cast( GROUP_CONCAT(distinct genusOrMonomial order by preferred desc) as char),h1.highertaxon as family,concat(h2.higherrank,':',h2.highertaxon) as higher,'common',country,locality,collectioncode,institutioncode,unitid,gatheringdate,cast( group_concat(url) as char),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 cast( GROUP_CONCAT(distinct genusOrMonomial order by preferred desc) as char) family = SELECT distinct highertaxon recordBasis = SELECT distinct cast( GROUP_CONCAT(distinct occurrenceid) as char), recordbasis unitsDetails = SELECT unitID, collectionID, institutionID FROM tripleidstoreid JOIN rawoccurrence ON fk_tripleidstoreid=tripleidstoreid getRecordBasis = SELECT distinct recordbasis unitsName=SELECT distinct canonicalname specimenOfTheDay = SELECT unitid,collectioncode,institutioncode,which_day FROM specimenOfTheDay s JOIN tripleidstore t on fk_tripleidstoreid=t.tripleidstoreid ORDER BY which_day desc limit 1; [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 rawcoordinates 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 rawhigher h1 ON h1.highertaxaid=fk_highertaxaid AND h1.higherrank like 'fami%' LEFT JOIN rawhigher h2 ON h2.highertaxaid=fk_highertaxaid AND h2.higherrank not like 'fami%' LEFT JOIN taxonAtomised t on i.fullscientificname=t.fullscientificname higherTaxon = FROM rawhigher 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 rawhigher JOIN identificationtohigher ON fk_highertaxaid=highertaxaid JOIN identification on fk_rawidentificationid=identificationid JOIN rawoccurrence ON occurrenceid=fk_occurrenceid recordBasis= FROM recordbasis JOIN rawoccurrence on fk_recordbasisid=recordbasisid getRecordBasis = FROM recordbasis taxonHigher = FROM rawhigher JOIN identificationtohigher ON fk_highertaxaid=highertaxaid JOIN identification ON identificationid=fk_rawidentificationid JOIN rawoccurrence ON fk_occurrenceid=occurrenceid [where] taxon= WHERE canonicalname taxonid = WHERE occurrenceid countRecordBasis = WHERE occurrenceid country= WHERE country collector= WHERE collectornames 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 taxonHigher=WHERE highertaxon taxonHigherGenus=WHERE genusOrMonomial taxonHigherFamily=WHERE higherrank like 'famil%' and highertaxon [or] taxon= OR canonicalname taxonName= OR canonicalname taxonHigher=OR highertaxon taxonHigherGenus=OR genusOrMonomial taxonHigherFamily=OR (higherrank like 'famil%' and highertaxon [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 country= GROUP BY occurrenceid collector= GROUP BY fk_occurrenceid institution = GROUP BY occurrenceid collection = GROUP BY occurrenceid occurrenceid = GROUP BY occurrenceid imagerecordbiorecordkey = GROUP BY occurrenceid higherTaxon = GROUP BY fk_occurrenceid genus= GROUP BY fk_occurrenceid family= GROUP BY fk_occurrenceid recordBasis = GROUP BY occurrenceid locality= GROUP BY occurrenceid [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] '= ' * = %