Untitled query #45318

SQL

use tewiki_p;

# This query retrieves all pages where there are only hidden cats or no cats at all
# 
# A page is not included in this query if it has already been temporarly categorized
# in "Category:Wikipedia:Nog te categoriseren sinds" (temp cat)

# Different types of "categorization":
# * Only normal cats                                              = permanently categorized
# * Normal and hidden cats                                        = permanently categorized
# * Hidden cats + "Category:Wikipedia:Nog te categoriseren sinds" = temporarly categorized
# * "Category:Wikipedia:Nog te categoriseren sinds"               = temporarly categorized
# * Only hidden cats or no cats at all                            = uncategorized (this query)

SELECT page.page_id, page.page_title, catlink.cl_to
FROM page
# Exclude all Category:Wikipedia cats from joining (usually maintenaince categories which are hidden)
# This will make catlink.cl_to NULL which then we can use to check in the WHERE clause
LEFT OUTER JOIN categorylinks catlink ON catlink.cl_from = page.page_id AND (
  catlink.cl_to NOT LIKE 'Wikipedia:%'
)
WHERE page.page_namespace = 0
AND page.page_is_redirect = 0
AND catlink.cl_to IS NULL
AND page.page_id NOT IN(
  SELECT pp_page
  FROM page_props
  WHERE pp_propname = 'disambiguation'
)
# Since "Category:Wikipedia:Nog te categoriseren sinds" was also left out, results only containing this category
# will be included in the result if we don't filter them out here
AND page.page_id NOT IN(
  SELECT catlink2.cl_from #same field as page_id
  FROM categorylinks catlink2
  WHERE catlink2.cl_to LIKE 'Wikipedia:Nog_te_categoriseren_sinds_%'
  OR catlink2.cl_to LIKE 'Wikipedia:Verwijderbaar%'
)
SQL

Checking query status...
