Pages of a main category's direct subcategories with ext link

Fork of Pages of a main category's direct subcategories by Arjunaraoc

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

SQL

use tewiki_p;
SELECT
     @a:=@a+1 serial_number, 
     CONCAT("[[",page_title,"]]") as page,
     cl_to as category

FROM page p
    (SELECT @a:= 0) AS a
JOIN categorylinks cl
ON p.page_namespace = 0 -- main page
AND cl.cl_from = p.page_id
      AND cl.cl_to in (
SELECT
  page_title as subcat
FROM page p
JOIN categorylinks cl
ON p.page_namespace = 14 -- Optional namespace check. 14 - categories, 6 - files
AND cl.cl_from = p.page_id
      AND cl.cl_to = "ప్రకాశం_జిల్లా_గ్రామాలు" -- Insert your category here.
      )

INNER JOIN revision ON p.page_latest = rev_id
INNER JOIN externallinks ON p.page_id = el_from
WHERE rev_deleted = 0 AND el_to LIKE "%www\.onefivenine\.com%";

SQL

Checking query status...