Dynamic Table Joining Based on Another Table Column Value?

Question

I'm trying to figure out if there is a simple way to dynamically load a 2nd table based on the column value of the first table with mysql

Servers (Table 1):

ID | Game | Title

Servers_1 (Table 2, option 1):

server_id (links to servers.id) | game_version | players | plugins

Servers_2 (Table 2, option 2):

server_id (links to servers.id) | game_version | players | mods | game_map

Servers_etc. (Table 2, option etc.)

Trying to figure out how to do something like

left_join servers_[servers.game] on servers.id = servers_[servers.game].server_id

So it would grab the value of servers.game and use that to finish the table name. If this is not possible, then is a case statement possible such as:

 Left_Join
   if ( servers.game == 1 ) 'servers_1'
   elseif ( servers.game == 2 ) 'servers_2'
   elseif ( servers.game == 3 ) 'servers_3'

Answer 1

One option would be to LEFT JOIN each of the tables and use a CASE statement to return the appropriate data.

Something like this should help get you started:

SELECT S.Id, S.Game, S.Title,
   CASE S.Game
      WHEN 1 THEN S1.game_version 
      WHEN 2 THEN S2.game_version 
   END game_version,
   ...
FROM Servers S
   LEFT JOIN Servers_1 S1 ON S.id = S1.Server_Id AND S.Game = 1
   LEFT JOIN Servers_2 S2 ON S.id = S2.Server_Id AND S.Game = 2

Instead of using CASE, you could probably just use COALESCE as each Id/Game should be unique and only 1 wouldn't be NULL:

SELECT COALESCE(S1.game_version,S2.game_version,...) game_version

If there is no way the same server id can be in multiple tables, then you can leave the AND S.Game... out of the LEFT JOINs as it wouldn't longer be needed. Depends on your unique keys.

Alternatively, you could use Dynamic SQL.