Fork of
(Third Attempt) Files with no machine-readable source, (excluding users that have opted out.)
by ShakespeareFan00
This query is marked as a draft
This query has been published
by ShakespeareFan00.
SQL
x
USE commonswiki_p;
SELECT
## CONCAT("User:",i.img_user_text) as username,
##p.page_latest,
##p.page_id,
##p.page_len,
CONCAT("File:",p.page_title) as page_title,
##i.img_name,
i.img_timestamp
##i.img_sha1
# Cross reference pages and images by page_tile ( I.E we want the Titular image for each 'page'
FROM page p
INNER JOIN image i ON i.img_name=p.page_title AND p.page_namespace=6
# only want files in a specfic category
INNER JOIN categorylinks c ON c.cl_from = p.page_id AND c.cl_to = 'Books_uploaded_by_Fæ'
#And with a specfic template
##INNER JOIN templatelinks t1 ON t1.tl_from = p.page_id
## AND t1.tl_namespace = 10
## AND t1.tl_title IN (
## Also exclude various deletion processes:
## 'Information')
#But that aren't also in other specified categories
LEFT JOIN categorylinks cnot ON cnot.cl_from = p.page_id AND cnot.cl_to
IN ( 'Books_uploaded_by_Fæ/reviewed'
'Public_domain_files_with_no_author_lifetime_information'
'PD_US_Government'
'Government_of_Canada_publications'
'PD_US_Census'
'Henry_G._Gilbert_Nursery_and_Seed_Trade_Catalog_Collection'
'Books_published_in_Boston'
'Books_from_New_York_(state)'
'National_Institutes_of_Health_images'
)
WHERE
##t1.tl_from IS NOT NULL AND
cnot.cl_from IS NULL
AND p.page_namespace = 6
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.