A Little Noise

October 6, 2006

Here Be Dragons

Filed under: MySQL Gotchas — snoyes @ 8:09 pm

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 ▼

4 Comments »

  1. here’s how i’d do it

    select
    locationId,
    coalesce(m3.mapName, m2.mapName, m1.mapName) as mapName
    from
    locations l
    left outer join map m1 on (l.continent=m1.continent)
    left outer join map m2 on (l.region=m2.region)
    left outer join map m3 on (l.country=m3.country)

    Comment by Darryl — October 6, 2006 @ 8:57 pm

  2. That also works, Darryl. I’d certainly call it more readable. Now to find out which one performs better.

    Comment by snoyes — October 6, 2006 @ 9:00 pm

  3. Just a quick thought, isnt north america more specific than the northern hemisphere :)

    Comment by BenjaminB — October 7, 2006 @ 1:48 am

  4. I actually am doing this right now, and I have 2 tables: “region” and “regionTree”. Basically, “region” is similar to your “map” table, and regionTree is a tree using the “nested set” model proposed by Mike Hillyer. And then you can just find the leaf node. So the regionTree in this case would be:

    1 Northern Hemisphere 12
    | \
    2 Europe 5 6 North America 11
    | |
    3 Sweden 4 7 USA 10
    |
    8 Pacific Northwest 9

    select Region,lft,rgt from RegionTree order by lft;
    would produce
    Northern Hemisphere,1,12
    Europe,2,5
    Sweden,3,4
    North America,6,11
    USA,7,10
    Pacific Northwest,8,9

    So to find all the leaf nodes you’d look for all the entries where rgt-lft=1

    Much better, and you can do a lot more that way.

    Comment by Sheeri — October 11, 2006 @ 1:49 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress