nlwiki Children's books

SQL

SELECT DISTINCT
	CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS page_title,
    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 '[Bb]oek_uit_[0-9]+$') yc ON page_id=yc.cl_from
    LEFT JOIN (SELECT cl_from, cl_to FROM categorylinks WHERE cl_to REGEXP 's_kinderboek$') cc ON page_id=cc.cl_from
WHERE 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...
