The famous nocturnal Hex hatch of the Midwest (and a few other lucky locations) stirs to the surface mythically large brown trout that only touch streamers for the rest of the year.
SELECT COUNT(imageid) FROM im_other WHERE shown_trout=0 && shown_fish=0 && private=0 && locationid=
SELECT COUNT(imageid) FROM im_uwphoto WHERE shown_trout=0 && locationid=
SELECT COUNT(imageid) FROM im_extax WHERE locationid=
SELECT COUNT(specimenid) FROM specimens WHERE locationid=
SELECT DISTINCT t.parentid AS parenttaxonid,
t.parentname AS parenttaxonname,
t.name AS taxonname,
t.level AS taxonlevel,
t.taxonid,
s.*,
i.*,
s.water AS location,
l.secret AS locationSecret,
l.name AS locationName,
l.state AS locationState
FROM rel_taxa_specimens r
INNER JOIN specimens s ON s.specimenid = r.specimenid
LEFT JOIN im_regspec i ON s.stat_bestimage = i.imageid
INNER JOIN taxa t ON t.taxonid = s.belongs_to
LEFT JOIN locations l ON l.id = s.locationid
WHERE s.locationid =
ORDER BY s.author_is_troutnut DESC, s.quality_tier DESC, s.stat_bestimage DESC # assume newer images are better
LIMIT 0, 12
SELECT COUNT(videoid) FROM videos WHERE locationid=