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

SQL

AخA
 
SELECT Pt.page_title as Template, Pa.page_title AS Article
FROM page Pt /* Template:Foo */
JOIN pagelinks ON pl_from = Pt.page_id AND pl_title = SUBSTRING(Pt.page_title,10) AND pl_namespace = 0 /* Template:Taxonomy/Foo has a wikilink to [[Foo]] */
JOIN page Pa ON Pa.page_title = pl_title AND Pa.page_namespace = 0 /* article Foo */
WHERE Pt.page_namespace=10 AND Pt.page_title like 'Taxonomy/%'
AND NOT EXISTS (
  SELECT 1
  FROM templatelinks
  JOIN linktarget ON lt_id = tl_target_id AND lt_namespace = 10
  AND lt_title IN ("Automatic_taxobox","Hybrid","Ichnobox","Infraspeciesbox","Speciesbox","Subspeciesbox","Taxobox","Virusbox","Taxobox_colour")
  WHERE tl_from = Pa.page_id
)
AND NOT EXISTS (
  SELECT 1
  FROM redirect
  JOIN page Pr ON Pr.page_title = rd_title AND Pr.page_namespace = 0
  JOIN templatelinks ON tl_from = Pr.page_id
  JOIN linktarget ON lt_id = tl_target_id AND lt_namespace = 10
  AND lt_title IN ("Automatic_taxobox","Hybrid","Ichnobox","Infraspeciesbox","Speciesbox","Subspeciesbox","Taxobox","Virusbox","Taxobox_colour")
  WHERE rd_from = Pa.page_id
)
AND NOT EXISTS (
  SELECT 1
  FROM categorylinks
  WHERE cl_from = Pa.page_id
  AND cl_to REGEXP "[Tt]ax(a|on)|[Ss]pecies|[Gg]en(us|era)|[Aa]nimal|[Mm]ammal|[Bb]ird|[Ff]amil(y|ies)|[Bb]iota|[Mm]esostigmata"
)
AND NOT EXISTS (
  SELECT 1
  FROM redirect
  JOIN page Pr ON Pr.page_title = rd_title AND Pr.page_namespace = 0
  JOIN categorylinks ON cl_from = Pr.page_id
  AND cl_to REGEXP "[Tt]ax(a|on)|[Ss]pecies|[Gg]en(us|era)|[Aa]nimal|[Mm]ammal|[Bb]ird|[Ff]amil(y|ies)|[Bb]iota|[Mm]esostigmata"
  WHERE rd_from = Pa.page_id
)
ORDER BY Pt.page_title, Pa.page_title
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...