SQL
AخA
/*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($|_)'*/
SELECT page_title, clk.cl_to AS cl_to, pp_value AS 'wikibase_item'
FROM categorylinks clk, page LEFT JOIN page_props ON (pp_page=page_id AND pp_propname='wikibase_item')
WHERE clk.cl_from=page_id AND clk.cl_to REGEXP 'e-eeuws_kinderboek$|^Kinderboek_uit_' AND NOT EXISTS (
SELECT * FROM categorylinks cly WHERE cly.cl_from=page_id AND cly.cl_to REGEXP '_uit_[0-9]{4}$')
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.