Stub-class critically endangered species articles

A list of critically endangered species with stub articles. You can use this query as a template for building your own niche-specific + class-specific searches.

SQL

# Category of the mainspace article
SET @MAIN_CAT = REPLACE("IUCN Red List critically endangered species", " ", "_");
# Class category
SET @CLASS_CAT = REPLACE("Stub-Class articles", " ", "_");

# GO
SELECT 
	p1.page_title AS "Page title",
    p2.page_title AS "TP title",
    cl2.cl_to AS "TEST"
FROM categorylinks cl1
LEFT JOIN page p1 ON cl1.cl_from = p1.page_id
INNER JOIN page p2 ON p2.page_title = p1.page_title AND p2.page_namespace = (p1.page_namespace + 1)
INNER JOIN categorylinks cl2 ON cl2.cl_from = p2.page_id AND ((
  SELECT COUNT(*) FROM categorylinks cls2 WHERE cls2.cl_to = @CLASS_CAT AND cls2.cl_from = (
  	SELECT ps2.page_id FROM page ps2 WHERE ps2.page_title = cl2.cl_to AND ps2.page_namespace = 14 # Category
  )
) > 0)
WHERE cl1.cl_to = @MAIN_CAT

SQL

Checking query status...