SQL grouping by list of preferred values

You don't need an outer join if you want to return only rows where 'MP3' or 'AAC'exist.

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.

Edit

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