(Third Attempt) Files with no machine-readable source, (excluding users that have opted out.)

Fork of Untitled query #30997 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_user_text,
##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 =  'Files_with_no_machine-readable_source'
#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 (  'All_non-free_media', 
      'Redirects_from_moves'
      'Wikipedia_restricted_images',
      'Wikipedia_restricted_images',
      'Wikipedia_image_placeholders',
      'DYK_images',
 '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',
 'Wikipedia_files_for_discussion',
 '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',
 'Wikipedia_files_on_Wikimedia_Commons_for_which_a_local_copy_has_been_requested_to_be_kept',
 'Items_with_OTRS_permission_confirmed', ## The authorship will be in the OTRS permissions ticket
 '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
 'Public_domain_images_no_longer_eligible_for_claim_of_authorship',
 '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.
'Self-published_work', ## These have the uploaders as authors.
'Copyright_holder_released_public_domain_files',
'Creative_Commons_Attribution-ShareAlike_files',
'Creative_Commons_Attribution-ShareAlike_2.0_files', 
'Creative_Commons_Attribution-ShareAlike_2.5_files',
'Creative_Commons_Attribution-ShareAlike_3.0_files', 
'Creative_Commons_Attribution-ShareAlike_4.0_files',
'CC-zero_files',
'Creative_Commons_Attribution_2.0_files',    
'Creative_Commons_Attribution_2.5_files',
'Creative_Commons_Attribution_3.0_files',
'Creative_Commons_Attribution_4.0_files',
'GFDL_files',  
'FAL_files',
'Pre-1978_without_copyright_notice_US_public_domain_images',
'All_User-created_public_domain_files',    
'User-created_public_domain_files',
'Public_domain_images_ineligible_for_copyright_(shape)'
'Wikipedia_files_tagged_as_own_work',
'Screenshots_of_Wikipedia',
'California_government_images',
'PD-Russia',
'PD-ZW-currency-exempt',
'PD_US_not_renewed',
'Australian_public_domain_photographs',
'Stamp_images_published_abroad_that_are_in_the_public_domain_in_the_United_States',
'PD_other_reasons',
'PD-Canada_images_with_unknown_US_copyright_status',
'Images_from_US_patents',
'Images_in_the_public_domain_in_Thailand'
)

                                             
WHERE 
##t1.tl_from IS NOT NULL AND 
cnot.cl_from IS NULL
AND p.page_namespace = 6
AND img_user_text NOT IN (
## Bots , which means the file history cannot be looked at automatically..
'718 Bot',
  
## User's opting out 
'Giano','Iridescent','Carrite',  'Andrew Davidson','Ritche333','Serial Number 54129','Bishonen',
  
## Removed for technical reasons.
'Titoxd',
  
## Already processed, or uploader aware.
'$?','$eti', 'Ritche333','Toiyabe', 'EdChem', 'Bcrowell'
)
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...
