Untitled query #30997

Fork of Untitled query #29748 by ShakespeareFan00
This query is marked as a draft This query has been published by ShakespeareFan00.
SQL

USE enwiki_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_timestamp, i.img_sha1
FROM  page p 
LEFT 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')

LEFT JOIN categorylinks cnot ON cnot.cl_from = p.page_id AND cnot.cl_to
IN (  'All_non-free_media', 
      'Redirects_from_moves'
##    'Wikipedia_files_for_discussion', 
##    'All_Wikipedia_files_missing_evidence_of_permission',
##   'All_Wikipedia_files_with_unknown_source',
##   'Files_with_poor_sources',
##   'All_files_proposed_for_deletion',
##   'Items_with_OTRS_permission_confirmed', ## The authorship will be in the OTRS permissions ticket
##    'All_Wikipedia_files_with_unknown_copyright_status',
##    'Wikipedia_files_with_disputed_copyright_information',
##    'Wikipedia_files_with_unconfirmed_permission_received_by_OTRS',
##    'Items_pending_OTRS_confirmation_of_permission',
##   'Wikipedia_files_requiring_splitting'
##    'Protected_sister_project_logos',   
##   'Wikipedia_fully-protected_files'

 ##   'Public_domain_images_ineligible_for_copyright_(simple)', ## Authorship is typically the brand owner for many of these 
 ##   'Public_domain_images_ineligible_for_copyright_(logo)',## Authorship is typically the brand owner for many of these 
 ##   'Public_domain_files_ineligible_for_copyright', ## Authorship may be irrelevant for these
 ##   'Images_in_the_public_domain_in_the_United_States', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
 ##   'All_free_in_US_media', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
 ##   'Author_died_more_than_100_years_ago_public_domain_files', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
 ##   'Author_died_more_than_70_years_ago_public_domain_files', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
 ##  'Pre-1996_PD_in_home_country_US_public_domain_images', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
 ##   'United_States_government_images', ## An Unknown Authorship field could be completed as "US Goverment media, specifc photographer uncredtied or not otherwsie noted"
 ##   'United_States_Air_Force_images', ## An Unknown Authorship field could be completed as "US Air Force, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_Navy_images',  ## An Unknown Authorship field could be completed as "US Navy, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_Army_images',  ## An Unknown Authorship field could be completed as "US Army, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_military_images',  ## An Unknown Authorship field could be completed as "US Deaprtment of Defence, specifc photographer/artist uncredtied or not otherwsie noted"
 ##  'Images_of_United_States_military_awards',
 ##  'NASA_images',  ## An Unknown Authorship field could be completed as "Courtesy of NASA, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'National_Oceanic_and_Atmospheric_Administration_images',  ## An Unknown Authorship field could be completed as "Courtesy of NOAA, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_Department_of_Energy_images',  ## An Unknown Authorship field could be completed as "Courtesy of US Department of Energy, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_Department_of_Justice_images',  ## An Unknown Authorship field could be completed as "Courtesy of US Department of Justice, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_Department_of_State_images',  ## An Unknown Authorship field could be completed as "Courtesy of US State Department, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'National_Institutes_of_Health_images',  ## An Unknown Authorship field could be completed as "Courtesy of National Institutes of Health, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_Geological_Survey_images',  ## An Unknown Authorship field could be completed as "Courtesy of US Geological Survey, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_Department_of_Transportation_images',## An Unknown Authorship field could be completed as "Courtesy of Department of Transportation", specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'United_States_Environmental_Protection_Agency_images', ## An Unknown Authorship field could be completed as "Courtesy of US Enivronmental Protection Agency, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'National_Park_Service_images', ## An Unknown Authorship field could be completed as "Courtesy of US National Parks Service, specifc photographer/artist uncredtied or not otherwsie noted"
 ##   'Images_of_the_United_States_Congress', ## An Unknown Authorship field could be completed as "US Congress, specifc photographer/artist uncredtied or not otherwsie noted"
##    'Spoken_articles', ## These have definite authors
##    'Self-images_that_have_been_claimed' ## These have the uploaders as authors.
 )
##LEFT JOIN imagelinks il ON il.il_from=p.page_id 
## JOIN image i ON il.il_to=i.img_name  AND p.page_title=i.img_name 
                                             
WHERE t1.tl_from IS NOT NULL
AND cnot.cl_from IS NULL
AND p.page_namespace = 6
LIMIT 50
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
