This query is marked as a draft This query has been published by Bdijkstra.

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.

Checking query status...