-- Media-DupThumbnailsList.sql

/* 2019-02-09 Tom Holden ve3meo

Builds a table of media files and their duplicates for further
processing by Media-MergeDuplicates.sql to eliminate duplicate
image files. Can only operate on image files for which thumbnails
have been generated in the RM database because it compares their
thumbnails. Does not address other file types.
*/
-- Duplicate Media List based on matching thumbnails (maybe risky compared to CCleaner and only does image files)
DROP TABLE IF EXISTS zDupMediaTable
;
CREATE TEMP TABLE zDupMediaTable
AS
SELECT MIN(M1.MediaID) PrimaryID, M1.MediaFile PrimaryFile, M2.MediaID DupID, M2.MediaFile DupFile
FROM MultiMediaTable M1
JOIN MultiMediaTable M2 
USING (Thumbnail)   -- ignores nulls
WHERE M1.MediaID < M2.MediaID
GROUP BY M2.MediaID    
;

-- List for info
SELECT * FROM zDupMediaTable
;

-- END of Script