nlwiki Children's books

SQL

SELECT DISTINCT
	CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS page_title,
    IFNULL(LEFT(ec.cl_to, 3),'') AS century,
    IFNULL(RIGHT(dc.cl_to, 9),'') AS decade,
    IFNULL(RIGHT(yc.cl_to, 4),'') AS year,
    CASE
      WHEN cc.cl_to='Amerikaans_kinderboek' THEN 'US'
      WHEN cc.cl_to='Nederlands_kinderboek' THEN 'NL'
      ELSE ''
	END AS 'country'
FROM categorylinks cl, page 
	LEFT JOIN (SELECT cl_from, cl_to FROM categorylinks WHERE cl_to REGEXP '^[0-9]+e-eeuws') ec ON page_id=ec.cl_from
	LEFT JOIN (SELECT cl_from, cl_to FROM categorylinks WHERE cl_to REGEXP '_uit_[0-9]{4}-[0-9]{4}$') dc ON page_id=dc.cl_from
	LEFT JOIN (SELECT cl_from, cl_to FROM categorylinks WHERE cl_to REGEXP '_uit_[0-9]+$') yc ON page_id=yc.cl_from
    LEFT JOIN (SELECT cl_from, cl_to FROM categorylinks WHERE cl_to REGEXP '^[A-Z][a-z]+s_kinderboek$') cc ON page_id=cc.cl_from
WHERE NOT EXISTS (SELECT * FROM categorylinks WHERE cl_from=page_id AND cl_to REGEXP 'reeks') AND 
	page_namespace=0 AND page_is_redirect=0 AND page_id=cl.cl_from AND cl.cl_to REGEXP '[Kk]inderboek($|_)'
SQL

Checking query status...
