USE commonswiki_p;

SELECT CONCAT('[[:File:', img_name, ']]') AS Name, img_timestamp AS UploadDate, user_registration AS RegistrationDate, img_description AS Origin, 'Not deleted' AS Status, tl_title AS Template, '' AS DeletionDate, '' AS DeletionReason FROM image

JOIN page ON page_title = img_name AND page_namespace = 6

JOIN user ON img_user = user_id

LEFT JOIN templatelinks ON page_id = tl_from AND tl_namespace = 10 AND tl_title IN ('Deletion_template_tag', 'No_permission_since', 'No_source_since', 'Copyvio')

WHERE img_timestamp > 20160719000000 AND img_timestamp < 20160727240000

AND img_description LIKE 'Cross-wiki upload from %'

UNION ALL

SELECT CONCAT('[[:File:', oi_name, ']]') AS Name, oi_timestamp AS Uploaddate, user_registration AS RegistrationDate, oi_description AS Origin, 'Overwritten' AS Status, tl_title AS Template, '' AS DeletionDate, '' AS DeletionReason FROM oldimage

JOIN page ON page_title = oi_name AND page_namespace = 6

JOIN user ON oi_user = user_id

LEFT JOIN templatelinks ON page_id = tl_from AND tl_namespace = 10 AND tl_title IN ('Deletion_template_tag', 'No_permission_since', 'No_source_since', 'Copyvio')

WHERE oi_timestamp > 20160719000000 AND oi_timestamp < 20160727240000

AND oi_description LIKE 'Cross-wiki upload from %'

UNION ALL

SELECT CONCAT('[[:File:', fa_name, ']]') AS Name, fa_timestamp AS Uploaddate, user_registration AS RegistrationDate, fa_description AS Origin, 'Deleted' AS Status, '' AS Template, fa_deleted_timestamp AS DeletionDate, fa_deleted_reason AS DeletionReason FROM filearchive

JOIN user ON fa_user = user_id

WHERE fa_timestamp > 20160719000000 AND fa_timestamp < 20160727240000

AND fa_description LIKE 'Cross-wiki upload from %'

GROUP BY Uploaddate