SQL grouping by list of preferred values

Question

First of all: I'm not quite sure what to put in the question title, I don't know how to call a query like this, perhaps that's why I couldn't find any answer.

I have a table of radio stations, and a table of streams. Each radio station can have multiple streams, for different formats, bitrates etc. I want to get a list of all stations, with a stream in the preferred format for a given application.

Now this is where it gets tricky, I want the preferred format to be a list, and my database should return the first suitable stream.

So I might have a list like this: ('MP3', 'AAC', 'OGG')

Then I want MySQL to return, for each station, the stream of type 'MP3', but if it does not exist it should return the 'AAC' stream for that station and so forth. If there is no suitable stream found, it should not return the station at al.

Example:

CREATE TABLE `stations` (
  `id` INT(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `streams` (
  `id` INT(11),
  `station` INT(11),
  `media_type` ENUM('MP3', 'OGG', 'AAC', 'Flash'),
  PRIMARY KEY (`id`),
  KEY (`station`),
  CONSTRAINT `fk_1` FOREIGN KEY (`station`) REFERENCES `stations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO `stations` (`id`) VALUES (1), (2), (3);
INSERT INTO `streams` (`id`, `station`, `media_type`) VALUES (1, 1, 'MP3'), (2, 1, 'AAC'), (3, 2, 'Flash'), (4, 2, 'AAC'), (5, 3, 'Flash');

I made a SQLFiddle here

If the preferred media type list is ('MP3', 'AAC'), then desired result using the example data above should be:

station stream  type
1       1       MP3
2       4       AAC

• Station 1 should have stream 1 of type MP3 (AAC is also supported, but MP3 is preferred over AAC)
• Station 2 should have stream 4 of type AAC (MP3 is not offered by station 2, but AAC is)
• Station 3 should not be in the result, as it only offers streaming via Flash

I tried this:

SELECT
    st.id AS station_id,
    str.id AS stream_id,
    str.media_type,
    FIELD(str.media_type, 'MP3', 'AAC') AS preference
FROM
    stations st
LEFT JOIN
    streams str ON str.station = st.id
GROUP BY 
    st.id
HAVING
    MIN(preference)

But that returns only 1 or 0 records depending wether the first record in the stream table is a preferred media type, I don't understand why.

The only solution I could find is ordering the streams using a subquery, and then grouping by station_id, like this:

SELECT sub.* FROM
    (SELECT
        st.id AS station_id,
        str.id AS stream_id,
        str.media_type
    FROM
        stations st
    LEFT JOIN
        streams str ON str.station = st.id
    WHERE
        str.media_type IN ('MP3', 'AAC')
    ORDER BY
        FIELD(str.media_type, 'MP3', 'AAC')
    ) AS sub
GROUP BY sub.station_id

But that would result in a full table scan of the temporary table being created by the subquery, the performance is unacceptable. Since we can't limit the inner query (since it isn't grouped yet), the temp table will get very big.

B.T.W., I'm running MySQL 5.6

So, what kind of query should I use to work with a list of preferred properties?

Answer 1

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_type
FROM stations st
JOIN streams str 
  ON str.station = st.id
WHERE -- 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.