Merging rows in SQL for similar IDs?

Question

I've got an interesting dilemma now. I have a database schema like the following:

GameList:
+-------+----------+-----------+------------+--------------------------------+
|  id   | steam_id | origin_id | impulse_id |           game_title           |
+-------+----------+-----------+------------+--------------------------------+
|   1   |   17450  |   NULL    |    NULL    |      Dragon Age: Origins       |
|   2   |   NULL   | 138994900 |    NULL    |    Dragon Age(TM): Origins     |
|   3   |   NULL   |   NULL    |  dragonage |      Dragon Age Origins        |
|   4   |   47850  | 201841300 |  fifamgr11 |        FIFA Manager 11         |
|  ...  |   ...    |    ...    |     ...    |              ...               |
+-------+----------+-----------+------------+--------------------------------+

GameAlias:
+----------+-----------+
|  old_id  |  new_id   |
+----------+-----------+
|    2     |     1     |
|    3     |     1     |
|   ...    |    ...    |
+----------+-----------+

Depending on whether the stores use the same title for the game there may be no issues, or there may be multiple rows for the same game. The Alias table exists to resolve this issue, by stating that id 2 and id 3 are just aliases for id 1.

What I need is an SQL query which uses both the GameList table and the GameAlias table and returns the following:

ConglomerateGameList:
+-------+----------+-----------+------------+--------------------------------+
|  id   | steam_id | origin_id | impulse_id |           game_title           |
+-------+----------+-----------+------------+--------------------------------+
|   1   |   17450  | 138994900 |  dragonage |      Dragon Age: Origins       |
|   4   |   47850  | 201841300 |  fifamgr11 |        FIFA Manager 11         |
|  ...  |   ...    |    ...    |     ...    |              ...               |
+-------+----------+-----------+------------+--------------------------------+

Note that I want the game title of the "new id". The game title for any "old ids" should simply be discarded/ignored.

I would also like to note that I can't make any modifications to the GameList table to solve this issue. If I were to simply re-write the table to look like my desired output then every night when I grab an updated game list from the stores it would fail to find the game in the database, generating yet another row like so:

+-------+----------+-----------+------------+--------------------------------+
|  id   | steam_id | origin_id | impulse_id |           game_title           |
+-------+----------+-----------+------------+--------------------------------+
|   1   |   17450  | 138994900 |  dragonage |      Dragon Age: Origins       |
|   4   |   47850  | 201841300 |  fifamgr11 |        FIFA Manager 11         |
|  ...  |   ...    |    ...    |     ...    |              ...               |
|  8139 |   NULL   | 138994900 |    NULL    |     Dragon Age(TM): Origins    |
|  8140 |   NULL   |    NULL   |  dragonage |      Dragon Age Origins        |
+-------+----------+-----------+------------+--------------------------------+

I also can't work on the assumption that a game's id will never change as Steam has been known to change them when a major update to the game is released.

Bonus points if it can recognize recursive aliases, like the following:

GameAlias:
+----------+-----------+
|  old_id  |  new_id   |
+----------+-----------+
|    2     |     1     |
|    3     |     2     |
|   ...    |    ...    |
+----------+-----------+

Since id 3 is an alias for id 2 which itself is an alias for id 1. If recursive aliases is impossible then I can just develop my application logic to prevent them.

score 2 · Accepted Answer · answered Oct 09 '11 at 16:54

2

Does this work? Correct the table names.

select ga1.new_id, max(gl1.steam_id), max(gl1.origin_id), max(gl1.impulse_id),
max(if(gl1.id = ga1.new_id,gl1.game_title,NULL)) as game_title
from gl1, ga1
where (gl1.id = ga1.new_id OR gl1.id = ga1.old_id)
group by ga1.new_id

union

select gl2.id, gl2.steam_id, gl2.origin_id, gl2.impulse_id, gl2.game_title
from gl2, ga2
where (gl2.id not in (
    select ga3.new_id from ga3
    union
    select ga4.old_id from ga4))

answered Oct 09 '11 at 16:54

Mosty Mostacho

42,742
16
96
123

Worked like a charm. In fact, I found a way to simplify the query a little further using `COALESCE()` to return the ID from the alias table or the original ID (if there was no alias for it). Although the `group by` and `max(gl1.steam_id), max(...` definitely helped me a lot. – stevendesu Oct 17 '11 at 01:24

Bogdan Sahlean · Answer 2 · 2011-10-09T21:18:59.067

1.First solution (without recursion):

CREATE TABLE GameList
(
     id         INT NOT NULL PRIMARY KEY
    ,steam_id   INT NULL
    ,origin_id  INT NULL
    ,impulse_id NVARCHAR(50) NULL            
    ,game_title NVARCHAR(50) NOT NULL
);
INSERT  GameList(id, steam_id, origin_id, impulse_id, game_title)
SELECT  1,  17450,  NULL,       NULL,       'Dragon Age: Origins'
UNION ALL
SELECT  2,  NULL,   138994900,  NULL,       'Dragon Age(TM): Origins'
UNION ALL
SELECT  3,  NULL,   NULL,       'dragonage','Dragon Age Origins'   
UNION ALL
SELECT  4,  47850,  201841300,  'fifamgr11','FIFA Manager 11';

CREATE TABLE GameAlias
(
    old_id INT NOT NULL PRIMARY KEY
    ,new_id INT NOT NULL
);

INSERT  GameAlias (old_id, new_id) VALUES (2,1);
INSERT  GameAlias (old_id, new_id) VALUES (3,1);

-- Solution 1
SELECT  COALESCE(ga.new_id, gl.id) new_id
        ,MAX(gl.steam_id) new_steam_id
        ,MAX(gl.origin_id) new_origin_id
        ,MAX(gl.impulse_id) new_impulse_id
        ,MAX( CASE WHEN ga.old_id IS NULL THEN gl.game_title ELSE NULL END ) new_game_title
FROM    GameList gl
LEFT OUTER JOIN GameAlias ga ON gl.id = ga.old_id
GROUP BY COALESCE(ga.new_id, gl.id);
-- End of Solution 1    
DROP TABLE GameList;
DROP TABLE GameAlias;

Results:

1   17450   138994900   dragonage   Dragon Age: Origins
4   47850   201841300   fifamgr11   FIFA Manager 11

2.Second solution (levels of recursion = three levels):

CREATE TABLE GameList
(
     id         INT NOT NULL PRIMARY KEY
    ,steam_id   INT NULL
    ,origin_id  INT NULL
    ,impulse_id NVARCHAR(50) NULL            
    ,game_title NVARCHAR(50) NOT NULL
);
INSERT  GameList(id, steam_id, origin_id, impulse_id, game_title)
SELECT  1,  17450,  NULL,       NULL,       'Dragon Age: Origins'
UNION ALL
SELECT  2,  NULL,   138994900,  NULL,       'Dragon Age(TM): Origins'
UNION ALL
SELECT  3,  NULL,   NULL,       'dragonage','Dragon Age Origins'   
UNION ALL
SELECT  4,  47850,  201841300,  'fifamgr11','FIFA Manager 11'
UNION ALL
SELECT  5,  11111,  NULL,       NULL,       'Starcraft 1'
UNION ALL
SELECT  6,  NULL,   1111111111, NULL,       'Starcraft 1.1'   
UNION ALL
SELECT  7,  NULL,   NULL,       NULL,      'Starcraft 1.2'
UNION ALL
SELECT  8,  NULL,   NULL,       'sc1',      'Starcraft 1.3';

CREATE TABLE GameAlias
(
    old_id INT NOT NULL PRIMARY KEY
    ,new_id INT NOT NULL
);

INSERT  GameAlias (old_id, new_id) VALUES (2,1);
INSERT  GameAlias (old_id, new_id) VALUES (3,1);
INSERT  GameAlias (old_id, new_id) VALUES (6,5);
INSERT  GameAlias (old_id, new_id) VALUES (7,6);
INSERT  GameAlias (old_id, new_id) VALUES (8,7);

-- Solution 2
CREATE TEMPORARY TABLE Mappings
(
    old_id INT NOT NULL PRIMARY KEY
    ,new_id INT NOT NULL
);
INSERT  Mappings (old_id, new_id)
-- first level mapping
SELECT  ga.old_id, ga.new_id
FROM    GameAlias ga
WHERE   ga.new_id NOT IN (SELECT t.old_id FROM GameAlias t)
-- second level mapping
UNION ALL
SELECT  ga.old_id, ga2.new_id
FROM    GameAlias ga
INNER JOIN GameAlias ga2 ON ga.new_id = ga2.old_id
WHERE   ga2.new_id NOT IN (SELECT t.old_id FROM GameAlias t)
-- third level mapping
UNION ALL
SELECT  ga.old_id, ga3.new_id
FROM    GameAlias ga
INNER JOIN GameAlias ga2 ON ga.new_id = ga2.old_id
INNER JOIN GameAlias ga3 ON ga2.new_id = ga3.old_id;

SELECT  COALESCE(ga.new_id, gl.id) new_id
        ,MAX(gl.steam_id) new_steam_id
        ,MAX(gl.origin_id) new_origin_id
        ,MAX(gl.impulse_id) new_impulse_id
        ,MAX( CASE WHEN ga.old_id IS NULL THEN gl.game_title ELSE NULL END ) new_game_title
FROM    GameList gl
LEFT OUTER JOIN Mappings ga ON gl.id = ga.old_id
GROUP BY COALESCE(ga.new_id, gl.id);

DROP TEMPORARY TABLE Mappings;
-- End of Solution 2

DROP TABLE GameList;
DROP TABLE GameAlias;

Results:

1   17450   138994900   dragonage   Dragon Age: Origins
4   47850   201841300   fifamgr11   FIFA Manager 11
5   11111   1111111111  sc1         Starcraft 1

I'm sorry, but MySQL doesn't has recursive queries/CTEs.

Merging rows in SQL for similar IDs?

2 Answers2