nlwiki Disambiguation pages with missing wikilinks

Fork of nlwiki Disambiguation pages with 0 or 1 outgoing wikilink by Bdijkstra

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

SQL

SELECT CONCAT('[[', REPLACE(dp_title,'_',' '), ']]') AS dp_title, CONCAT('[[', REPLACE(lp_title,'_',' '), ']]') AS lp_title
FROM (
  		SELECT page_id AS dp_id, page_title AS dp_title
		FROM page, page_props
  		WHERE page_namespace=0 AND page_is_redirect=0 AND pp_page=page_id AND pp_propname='disambiguation'
  	) dp, (
    	SELECT page_title AS lp_title
    	FROM page
      	WHERE
      		page_namespace=0 AND 
      		page_is_redirect=0 AND
      		page_title REGEXP '_\\(.*\\)$' AND
      		NOT EXISTS (SELECT * FROM page_props WHERE pp_page=page_id AND pp_propname='disambiguation')
  	) lp
WHERE
	dp_title!=lp_title AND
    REGEXP_REPLACE(dp_title, '_\\(.*\\)$', '')=REGEXP_REPLACE(lp_title, '_\\(.*\\)$', '') AND
    NOT EXISTS (SELECT * FROM pagelinks WHERE pl_from=dp_id AND pl_namespace=0 AND pl_title=lp_title)
LIMIT 50

SQL

Checking query status...