SQL grouping by list of preferred values
You don't need an outer join if you want to return only rows where
This is a Standard SQL solution which will work as-is in mysql, see fiddle:
SELECT st.id AS station_id, COALESCE(MAX(CASE WHEN str.media_type = 'MP3' THEN str.id END) ,MAX(CASE WHEN str.media_type = 'AAC' THEN str.id END) ) AS stream_id, COALESCE(MAX(CASE WHEN str.media_type = 'MP3' THEN str.media_type END) ,MAX(CASE WHEN str.media_type = 'AAC' THEN str.media_type END) ) AS media_typeFROM stations stJOIN streams str ON str.station = st.idWHERE -- only stations with the requested media types str.media_type IN ('MP3', 'AAC')GROUP BY st.id
It's easy to add more media types, mainly cut & paste. The COALESCE returns the first matching media type based on the order of CASEs.
To get the highest preference stream per radio station, you can use a variable to rank each stream per radio station according to its
media_type value and only select rows with rank 1:
select * from ( select *, @rn := if(@prevStationId = station_id, @rn+1, 1) rn, @prevStationId := station_id from streams where media_type in ('MP3','AAC') order by station_id, FIELD(media_type,'MP3','AAC')) t1 where rn = 1