[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] '= ' * = % [select] taxon = SELECT cast( group_concat(distinct canonicalname order by preferred desc) as char),cast( GROUP_CONCAT(distinct occurrenceid) as char), COUNT(distinct occurrenceid) all = SELECT straight_JOIN 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,vernacularName,country,locality,collectioncode,institutioncode,occurrenceid,occurrenceid from taxon genus = SELECT straight_JOIN DISTINCT genusOrMonomial family = SELECT straight_JOIN DISTINCT family higherTaxon = SELECT straight_JOIN rawhigherTaxon commonName= SELECT straight_JOIN vernacularName country = SELECT straight_JOIN DISTINCT country institution = SELECT DISTINCT straight_JOIN institutioncode collection = SELECT DISTINCT straight_JOIN collectioncode collector= SELECT DISTINCT straight_JOIN collectornames type = SELECT straight_JOIN COUNT(distinct occurrenceid),recordBasis occurrenceid =SELECT straight_JOIN DISTINCT occurrenceid full = SELECT SQL_NO_CACHE unitid, 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,gatheringDate,cast( group_concat(url) as char),longitude,latitude,replace(concat('(',IFNULL(t.bracketauthorship,''),')',IFNULL(t.author,'')),'()',''),recordBasis,collectornames,collectornumber,fieldNumber, occurrenceid FROM rawoccurrence JOIN recordbasis on fk_recordbasisid=recordbasisid JOIN identification i ON fk_occurrenceid=occurrenceid JOIN tripleidstore on fk_tripleidstoreid=tripleidstoreid LEFT JOIN taxonAtomised t on i.fullscientificname=t.fullscientificname nbMulti = SELECT straight_JOIN COUNT(DISTINCT imagerecordurl) FROM imageRecord countRecordBasis = SELECT straight_JOIN COUNT(distinct occurrenceid),recordBasis countbioRecordKey = SELECT straight_JOIN DISTINCT COUNT(distinct occurrenceid) unitsDetails = SELECT straight_JOIN occurrenceid,institutioncode,collectioncode FROM tripleidstore JOIN rawoccurrence ON tripleidstoreid=fk_tripleidstoreid countUnits = SELECT COUNT(distinct occurrenceid) from rawoccurrence cacheDetails=SELECT unitid, 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,gatheringDate,cast( group_concat(url) as char),longitude,latitude,replace(concat('(',IFNULL(t.bracketauthorship,''),')',IFNULL(t.author,'')),'()',''),recordBasis,collectornames,collectornumber,fieldNumber, occurrenceid FROM rawoccurrence JOIN recordbasis on fk_recordbasisid=recordbasisid JOIN identification i ON fk_occurrenceid=occurrenceid JOIN tripleidstore on fk_tripleidstoreid=tripleidstoreid LEFT JOIN rawcoordinates c on c.fk_occurrenceid=occurrenceid LEFT JOIN multimedia m on m.fk_tripleidstoreid=tripleidstoreid LEFT JOIN collectors co ON co.fk_occurrenceid=occurrenceid 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 dlCache= SELECT unitid, 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,gatheringDate,cast( group_concat(url) as char),longitude,latitude,replace(concat('(',IFNULL(t.bracketauthorship,''),')',IFNULL(t.author,'')),'()',''),recordBasis,collectornames,collectornumber,fieldNumber, occurrenceid FROM rawoccurrence JOIN recordbasis on fk_recordbasisid=recordbasisid JOIN identification i ON fk_occurrenceid=occurrenceid JOIN tripleidstore on fk_tripleidstoreid=tripleidstoreid LEFT JOIN rawcoordinates c on c.fk_occurrenceid=occurrenceid LEFT JOIN multimedia m on m.fk_tripleidstoreid=tripleidstoreid LEFT JOIN collectors co ON co.fk_occurrenceid=occurrenceid 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 dlCDM= SELECT unitid, 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,gatheringDate,cast( group_concat(url) as char),longitude,latitude,replace(concat('(',IFNULL(t.bracketauthorship,''),')',IFNULL(t.author,'')),'()',''),recordBasis,collectornames,collectornumber,fieldNumber, occurrenceid FROM rawoccurrence JOIN recordbasis on fk_recordbasisid=recordbasisid JOIN identification i ON fk_occurrenceid=occurrenceid JOIN tripleidstore on fk_tripleidstoreid=tripleidstoreid LEFT JOIN rawcoordinates c on c.fk_occurrenceid=occurrenceid LEFT JOIN multimedia m on m.fk_tripleidstoreid=tripleidstoreid LEFT JOIN collectors co ON co.fk_occurrenceid=occurrenceid 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 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] occurrence= FROM rawoccurrence JOIN identification r ON r.fk_occurrenceid = occurrenceid JOIN tripleidstore ON fk_tripleidstoreid = tripleidstoreid JOIN recordbasis ON fk_recordbasisid=recordbasisid LEFT JOIN collectors c ON c.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 r.fullscientificname=t.fullscientificname occurrenceMedia = FROM rawoccurrence JOIN identification r ON r.fk_occurrenceid = occurrenceid JOIN tripleidstore ON fk_tripleidstoreid = tripleidstoreid JOIN recordbasis ON fk_recordbasisid=recordbasisid LEFT JOIN collectors c ON c.fk_occurrenceid=occurrenceid 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 r.fullscientificname=t.fullscientificname taxonHigher = FROM rawhigher JOIN identificationtohigher ON fk_highertaxaid=highertaxaid JOIN identification ON identificationid=fk_rawidentificationid JOIN rawoccurrence ON fk_occurrenceid=occurrenceid [where] taxonKeybioRecord = WHERE occurrenceid taxonKeytaxon = WHERE fk_occurrenceid taxonName = WHERE canonicalname taxonid = WHERE fk_occurrenceid higherTaxon = WHERE highertaxaid occurrenceid = WHERE occurrenceid unitID = WHERE unitid occurrenceid = WHERE occurrenceid bioKey = WHERE occurrenceid multimedia = WHERE m.url is not null collector= WHERE collectornames locality= WHERE locality taxonHigher=WHERE highertaxon taxonHigherGenus=WHERE genusOrMonomial taxonHigherFamily=WHERE higherrank like 'famil%' and highertaxon country= WHERE country collection= WHERE collectioncode institution= WHERE institutioncode [whereGroup] taxonName = WHERE (canonicalname [or] taxonName = OR canonicalname unitID = OR unitid taxonHigher=OR highertaxon taxonHigherGenus=OR genusOrMonomial taxonHigherFamily=OR (higherrank like 'famil%' and highertaxon [and] institution= AND institutioncode collection = AND collectioncode locality = AND locality localityFT = AND match(locality) against country = AND country dates = AND gatheringDate BETWEEN %i AND %i dateInf = AND gatheringDateEnd < dateEq = AND gatheringDate = dateSup = AND gatheringDateBegin > coordinates = AND latitude < %s and latitude > %s AND longitude < %s AND longitude > %s recordBasis = AND recordBasis occurrenceid= AND occurrenceid collector= AND collectorname collectorFT = AND match(collectorname) against family = AND family genus = AND genus taxonName = AND canonicalname multimedia = AND m.url is not null higherTaxon = AND highertaxon commonName = AND vernacularName collNumber = AND collectornumber fieldNumber = AND fieldNumber [andGroup] institution= AND (institutioncode collection = AND (collectioncode locality = AND (locality localityFT = AND match(locality) against country = AND (country dateInf = AND (gatheringDateEnd < dateEq = AND (gatheringDate = dateSup = AND (gatheringDateBegin > occurrenceid= AND (occurrenceid collector= AND (collectornames collectorFT = AND match(collectornames) against family = AND (family genus = AND (genus taxonName = AND (canonicalname higherTaxon = AND (highertaxon commonName = AND (vernacularName recordBasis = AND (recordBasis collNumber = AND (collectornumber fieldNumber = AND (fieldNumber occurrenceid = AND (occurrenceid [list] country = SELECT DISTINCT country FROM rawoccurrence institution = SELECT DISTINCT institutioncode from tripleidstore collection =SELECT DISTINCT collectioncode from tripleidstore family = SELECT distinct highertaxon from higher where higherrank like 'famil%' [orNull] recordBasis = OR recordBasis is null locality = OR locality is null #country = OR (country is null or country is null) country = OR (country is null or country is null) collector= OR collectornames is null family = OR family is null genus = OR genus is null taxonName = OR canonicalname is null higherTaxon = OR highertaxon is null commonName = OR vernacularName is null coordinates = OR (latitude is null and longitude is null) [andNull] recordBasis = AND recordBasis is null locality = AND locality is null #country = AND (country is null or country is null) country = AND (country is null or country is null) collector= AND collectornames is null family = AND family is null genus = AND genus is null taxonName = AND canonicalname is null higherTaxon = AND highertaxon is null commonName = AND vernacularName is null coordinates = AND latitude is null and longitude is null [groupBy] countRecordBasis = GROUP BY recordBasis recordBasis = GROUP BY recordBasis taxonName = GROUP BY fk_occurrenceid taxon = GROUP BY occurrenceid dlCache = GROUP BY occurrenceid ,tripleidstoreid occurrenceID = GROUP BY occurrenceid [thesaurus] Euro+Med Plantbase=SELECT NameCache from Name WHERE NameCache LIKE Fauna Europaea=SELECT NameCache from FaEuFull WHERE NameCache LIKE Standardliste=SELECT NameCache from Name WHERE NameCache LIKE Euro+Med Plantbase wa=SELECT NameCache from Name WHERE NameCache LIKE Fauna Europaea wa=SELECT NameCache from FaEuFull WHERE NameCache LIKE Standardliste wa=SELECT NameCache from Name WHERE NameCache LIKE ERMS=SELECT tu_displayname FROM tu WHERE tu_displayname LIKE ERMS wa=SELECT tu_displayname FROM tu WHERE tu_displayname LIKE Kew = SELECT NameCache from dbo.toqe_view WHERE NameCache LIKE Kew wa = SELECT NameCache from dbo.toqe_view WHERE NameCache LIKE