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.

So you want all files regardless of whether they're thumbnail or original, but you want to sort by thumbnail first? — AdamMc331, Jun 25 '15 at 15:54
Yes, if there is a thumbnail - show that, if no thumbnail but there is a preview show that, then finally if no preview simply show the original file. — MF_it, Jun 25 '15 at 16:02
assuming they won't have multiple thumbnails or originals and there is no other type.... `select f.*, coalesce(s_thumb.source_url, s_orig.source_url) as source_url from files f left outer join source s_thumb on s_thumb.file_id = f.file_id and s_thumb.source_size='thumbnail' left outer join source s_orig on s_orig.file_id = f.file_id and s_orig.source_size='original'` — gloomy.penguin, Jun 25 '15 at 16:30
that's not the ideal way to do that, though. bc scalability. i would [look into how mysql does ranking](http://stackoverflow.com/questions/1895110/row-number-in-mysql) and use a case statement to set your source size preferences and then take the top for each group by file_id. — gloomy.penguin, Jun 25 '15 at 16:32

score 0 · Accepted Answer · answered Jun 26 '15 at 13:38

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

Mysql JOIN tables with preference and a GROUP BY

1 Answers1