A Little Noise

I have a table of maps:

CREATE TABLE map (
    mapName varchar(255),
    mapType ENUM('continent', 'region', 'country'),
    continent varchar(255) NULL,
    region varchar(255) NULL,
    country varchar(255) NULL
);
INSERT INTO map VALUES 
    ('Europe', 'continent', 'Europe', NULL, NULL),
    ('North America', 'continent', 'North America', NULL, NULL),
    ('Northern Hemisphere', 'region', NULL, 'Northern Hemisphere', NULL),
    ('Sweden', 'country', NULL, NULL, 'Sweden'),
    ('Mexico', 'country', NULL, NULL, 'Mexico');

And a table of locations:

CREATE TABLE locations (
    locationId int auto_increment primary key,
    continent varchar(255),
    region varchar(255),
    country varchar(255)
);

INSERT INTO locations VALUES 
    (NULL, 'Europe', 'Northern Hemisphere', 'Sweden'),
    (NULL, 'North America',  'Northern Hemisphere', 'Canada'),
    (NULL, 'North America',  'Pacific Northwest', 'USA');

Obviously, this example is denormalized – in the real world, I’d use a surrogate key and store continentId, regionId, and countryId. Even then, it’s not the ideal layout for this application. But that’s just part of the challenge.

Here’s the goal: find the most specific map for each location. By most specific, I mean use a country map if available. If not, use a region map. Use a continent map as a last resort.

So, the result set should be:

+------------+---------------------+
| locationId | mapName             |
+------------+---------------------+
|          1 | Sweden              |
|          2 | Northern Hemisphere |
|          3 | North America       |
+------------+---------------------+

Bonus points if it works pre-4.1

Hint ▼

Show Answer ▼

SELECT 
    locationId,
    m.mapName
FROM 
    locations l
    JOIN map m ON (
        l.continent = m.continent
        OR l.region = m.region
        OR l.country = m.country
    )
    LEFT JOIN map m2 ON (
        (l.continent = m2.continent
        OR l.region = m2.region
        OR l.country = m2.country)
        AND CAST(m.mapType AS unsigned) < CAST(m2.mapType AS unsigned)
    )
WHERE
    m2.mapType IS NULL
GROUP BY 
    locationId;