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 dp_ns, dp_title, lp_ns, lp_title
FROM (
  		SELECT
  			page_id AS dp_id,
  			page_namespace AS dp_ns,
  			#page_title AS dp_title,
  			REGEXP_REPLACE(page_title, '_\\(.*\\)$', '') AS dp_label
		FROM page, page_props
  		WHERE page_is_redirect=0 AND pp_page=page_id AND pp_propname='disambiguation'
  	) dp, (
    	SELECT
      		page_namespace AS lp_ns,
      		#page_title AS lp_title,
      		REGEXP_REPLACE(page_title, '_\\(.*\\)$', '') AS lp_label
    	FROM page
      	WHERE
      		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_ns=lp_ns AND
    #dp_title!=lp_title AND
    dp_label=lp_label AND
    NOT EXISTS (SELECT * FROM pagelinks WHERE pl_from=dp_id AND pl_namespace=lp_ns AND pl_title=lp_title)

SQL

Checking query status...