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