Untitled query #4660

SQL

use commonswiki_p;

SELECT /* SLOW_OK */  concat("Template:",tmpl.tl_title)
FROM page file1
INNER JOIN categorylinks cl1  -- file in Files_with_no_machine-readable_license category
  ON 
    cl1.cl_from = file1.page_id and  
    cl1.cl_to ="Files_with_no_machine-readable_license"
INNER JOIN templatelinks tmpl 
  ON 
    tmpl.tl_from = file1.page_id AND -- template was transcluded in file1
	tmpl.tl_namespace = 10 AND
    tmpl.tl_title in (
      SELECT license.page_title -- list of license templates
      FROM 
        page license
        INNER JOIN templatelinks ON tl_from = license.page_id
      WHERE
        tl_title = 'License_template_tag' and
		license.page_title not like "%/%" and -- no subtemplates
        license.page_namespace = 10
	)
 group by tmpl.tl_title
SQL

Checking query status...
