USE enwiki_p;

SELECT p.page_id, p.page_len, CONCAT("File:",p.page_title) as page_title, img_timestamp FROM page p 

INNER JOIN categorylinks c ON c.cl_from = p.page_id AND c.cl_to =  'Files_with_no_machine-readable_source'

LEFT JOIN categorylinks cnot ON cnot.cl_from = p.page_id AND cnot.cl_to

IN ('Wikipedia_files_tagged_as_own_work', 

    'All_non-free_media', 

    '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',

    'Redirects_from_moves',

    'Items_with_OTRS_permission_confirmed',

    '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',

    'Spoken_articles',

    'Self-images_that_have_been_claimed'

   )

LEFT JOIN templatelinks t ON t.tl_from = p.page_id

                          AND t.tl_namespace = 10

                          AND t.tl_title IN (

                        # Also exclude various deletion processes:

                            'Di-no_source',

                             'Deletable_file', 

                             'Now_Commons', 

                            # Also exclude files with disputed copyright status...

                             'NFUR_not_needed',

                            # ...,Information presented in others ways,...

                            'Google_Art_Project',

                            'Painting',

                            'Wikipedia-screenshot',

                            'Used_in_system','Pp-upload','Pp-template',

                            #.. and stuff that's less of a priority, or issue for other reasons.

                            'Keep_local_high-risk',

                            'Img-claimed',

                            'Img-unclaimed',

                            'Media_by_uploader',

                            'Imgnote-hassource',

                            'OTRS_source'

                                            ) 

JOIN image on img_timestamp < 20050918000000 and page_title = img_name

WHERE t.tl_from IS NULL

AND cnot.cl_from IS NULL

AND p.page_namespace = 6

AND p.page_id < 2697778