Mysql JOIN tables with preference and a GROUP BY

Question

I'm working on an file/image database. I have one table that indexes the file names and attaches them to an 'album'

FILES TABLE
file_id | file_name | album_id
1       | image x   |  2
2       | image y   |  2

The second table stores the location and various sizes of the files

SOURCE TABLE
source_id | file_id | source_size | source_url
1         |  1      | original    | //... 
2         |  1      | thumbnail   | //... 
3         |  2      | original    | //... 
4         |  3      | original    | //... 

Currently the 'source_size' column is listed as text (original, thumbnail, preview etc..).

I'm struggling to query the tables with a preference on the source_size while still returning all files within that album.

So far it looks something like this:

SELECT 
f.*, s.*
FROM source s
INNER JOIN files f ON f.file_id=s.file_id 
WHERE f.album_id="2" 
GROUP BY s.file_id
ORDER BY FIND_IN_SET(s.source_size,"thumbnail, original") DESC

I would like it to return the source_id of the file in thumbnail format first but failing that simply return the original size file where it does not find a match.

In future queries it may be with a preference of originals or previews.

Answer 1

This seems to work for the moment but not sure if it's right...

    SELECT 
    f.*, fs*
    FROM files f
    INNER JOIN (SELECT s.* FROM file_source s ORDER BY FIND_IN_SET(s.source_size,"thumbnail, original") DESC) fs ON f.file_id=fs.file_id 
    WHERE f.album_id="2" 
    GROUP BY f.file_id
    ORDER BY f.file_id DESC