SELECT CONCAT('Template:', p1.page_title), p2.page_is_redirect AS 'exists as redirect'

FROM page p1

LEFT JOIN page AS p2 ON p2.page_namespace = 0 AND p2.page_title = SUBSTRING(p1.page_title, LENGTH('Editnotices/Page/') + 1)

WHERE p1.page_namespace = 10  -- template

  AND p1.page_title LIKE 'Editnotices/Page/%'

  -- exclude editnotices with a colon since they are not in mainspace. for example, exclude Editnotice/Page/Category:Test

  AND p1.page_title NOT LIKE '%Talk:%'

  AND p1.page_title NOT LIKE '%User:%'

  AND p1.page_title NOT LIKE '%Wikipedia:%'

  AND p1.page_title NOT LIKE '%File:%'

  AND p1.page_title NOT LIKE '%MediaWiki:%'

  AND p1.page_title NOT LIKE '%Template:%'

  AND p1.page_title NOT LIKE '%Help:%'

  AND p1.page_title NOT LIKE '%Category:%'

  AND p1.page_title NOT LIKE '%Portal:%'

  AND p1.page_title NOT LIKE '%Draft:%'

  AND p1.page_title NOT LIKE '%MOS:%'

  AND p1.page_title NOT LIKE '%TimedText:%'

  AND p1.page_title NOT LIKE '%Module:%'

  AND p1.page_title NOT LIKE '%User_talk:%'

  AND p1.page_title NOT LIKE '%Wikipedia_talk:%'

  AND p1.page_title NOT LIKE '%File_talk:%'

  AND p1.page_title NOT LIKE '%MediaWiki_talk:%'

  AND p1.page_title NOT LIKE '%Template_talk:%'

  AND p1.page_title NOT LIKE '%Help_talk:%'

  AND p1.page_title NOT LIKE '%Category_talk:%'

  AND p1.page_title NOT LIKE '%Portal_talk:%'

  AND p1.page_title NOT LIKE '%Draft_talk:%'

  AND p1.page_title NOT LIKE '%MOS_talk:%'

  AND p1.page_title NOT LIKE '%TimedText_talk:%'

  AND p1.page_title NOT LIKE '%Module_talk:%'

  AND (p2.page_is_redirect IS NULL -- doesn't exist

       OR (p2.page_is_redirect = 1 -- exists as redirect

           AND p1.page_is_redirect = 0)) -- but the editnotice isn't also a redirect

ORDER BY p1.page_title ASC;