Pokemon Species Summary

SELECT
 species.identifier AS 'id',
 lang.name AS 'language',
 COALESCE((
  SELECT species_entry.flavor_text
  FROM pokemon_species_flavor_text AS 'species_entry'
  WHERE species_entry.language_id==lang.language_id
  GROUP BY species_entry.species_id
  HAVING species_entry.version_id==MAX(species_entry.version_id)
 ),(
  SELECT 'EN:'||species_entry.flavor_text
  FROM pokemon_species_flavor_text AS 'species_entry'
  WHERE species_entry.language_id==9
  GROUP BY species_entry.species_id
  HAVING species_entry.version_id==MAX(species_entry.version_id)
)) AS 'pkdx',
 COALESCE((
  SELECT species_names.name
  FROM pokemon_species_names AS 'species_names'
  WHERE
   species_names.local_language_id==lang.language_id
  AND
   species_names.pokemon_species_id==species.id
  ),(
  SELECT 'EN:'||species_names.name
  FROM pokemon_species_names AS 'species_names'
  WHERE
   species_names.local_language_id==9
  AND
   species_names.pokemon_species_id==species.id
 )) AS 'name',
 stats.identifier AS 'base',
 (
  SELECT pokemon_stats.base_stat
  FROM pokemon_stats
  WHERE
   pokemon_stats.pokemon_id==species.id
  AND
   pokemon_stats.stat_id==stats.id
 ) AS 'stat'
 FROM
  pokemon_species AS 'species'
 JOIN stats
 JOIN
  language_names AS 'lang'
 WHERE stats.is_battle_only==0
 ORDER BY id ASC
/* Use with Veekun's database! */