Page Comparison

SELECT
 r.renditionID mediaID,
 r.renditionNumber identificationNumber,
 f.fileName filename,
 SUBSTRING(p.Path, 4, LEN(p.Path) - 3) 'path',
 f.fileDate dateCreated,
 t.format format,
 lower(y.mediaType) type,
 MAX(CASE WHEN m.approvedForWeb = 1 THEN 'yes' ELSE 'no' END) approvedForWeb,
 MAX(CASE WHEN m.copyright LIKE 'Benjamin M. Auerbach%' THEN 9223
       WHEN m.copyright LIKE 'Brian Burd%' THEN 9225
       WHEN m.copyright LIKE 'Darren Modzelewski%' THEN 8506
       WHEN m.copyright LIKE 'Smithsonian Institution Archives%' THEN 9224
       WHEN m.copyright LIKE 'Phoebe A. Hearst Museum of Anthropology%' THEN 8107
       WHEN m.copyright LIKE '______/ Phoebe A. Hearst Museum of Anthropology%' THEN 8107
       END) rightsHolder,
 MAX(CASE WHEN m.copyright LIKE '%/ %' THEN 'rights holder: ' + m.copyright END) copyrightStatement
FROM
 MediaRenditions r
 JOIN MediaFiles f ON r.primaryFileID = f.fileID
 INNER JOIN dbo.MediaPaths p ON f.PathID = p.PathID 
 LEFT OUTER JOIN MediaFormats t ON f.formatID = t.formatID
 LEFT OUTER JOIN MediaTypes y ON r.mediaTypeID = y.mediaTypeID
 LEFT OUTER JOIN MediaMaster m ON r.renditionID = m.primaryRendID
GROUP BY r.renditionID, r.renditionNumber, f.fileName, p.path, f.fileDate, t.format, y.mediaType
;