Unregistered users page or subpage:bnwiki

তালিকায় কয়েকটি নিবন্ধিত একাউন্টও থাকতে পারে...

SQL

USE bnwiki_p;
SELECT 
	page_namespace, 
CONCAT("[[ব্যবহারকারী:",page_title,"|",page_title,"]]") AS page_title, 
    GROUP_CONCAT(REPLACE(cl_to,'_',' ') SEPARATOR ' | ') AS cl_to
FROM `page` LEFT JOIN `categorylinks` ON page_id=cl_from
WHERE 
	page_is_redirect=0 AND
	page_namespace IN (2, 3) AND
    page_title NOT REGEXP '([1-9][0-9\.]+[0-9]|[1-9A-F][0-9A-F:]+[0-9A-F])(/[0-9]+)?' AND
    NOT EXISTS (SELECT * FROM `user` WHERE
                user_name=REGEXP_REPLACE(REPLACE(page_title,'_',' '), ' */.*$', '')
               )
GROUP BY page_id
ORDER BY page_id DESC	# new pages on top

SQL

Checking query status...