A single MySQL query for 'bouncing' table selects

Question

So, say for the sake of simplicity, I have a master table containing two fields - The first is an attribute and the second is the attributes value. If the second field is set to reference a value in another table it is denoted in parenthesis.

Example:

                 MASTER_TABLE:
Attr_ID |  Attr_Val
--------+-----------
   1    | 23(table1)   --> 23rd value from `table1`
   2    | ...
   1    | 42           --> the number 42
   1    | 72(table2)   --> 72nd value from `table2`
   3    | ...
   1    | txt          --> string "txt"
   2    | ...
   4    | ...


                 TABLE 1:
 Val_Id |  Value
--------+-----------
   1    | some_content
   2    | ...
   .    | ...
   .    | ...
   .    | ...
  23    | some_content
   .    | ...

Is it possible to perform a single query in SQL (without parsing the results inside the application and requerying the db) that would iterate trough master_table and for the given <attr_id> get only the attributes that reference other tables (e.g. 23(table1), 72(table2), ...), then parse the tables names from the parenthesis (e.g. table1, table2, ...) and perform a query to get the (23rd, 72nd, ...) value (e.g. some_content) from that referenced table?

Here is something I've done, and it parses the Attr_Val for the table name, but I don't know how to assign it to a string and then do a query with that string.

PREPARE pstmt FROM 
    "SELECT * FROM information_schema.tables 
     WHERE TABLESCHEMA = '<my_db_name>' AND TABLE_NAME=?"; 

 SET @str_tablename = 
   (SELECT table.tablename FROM
       (SELECT @string:=(SELECT <string_column> FROM <table> WHERE ID=<attr_id>) as String,
            @loc1:=length(@string)-locate("(", reverse(@string))+2 AS from,
            @loc2:=length(@string)-locate(")", reverse(@string))+1-@loc1 AS to,
            substr(@string,@loc1, @loc2) AS tablename
       ) table
   );  <--this returns 1 rows which is OK

EXECUTE pstmt USING @str_tablename; <--this then returns 0 rows

Any thoughts?

Answer 1

I love the purity of this approach, if pulled off. But I'm thinking you're creating a maintenance bomb. With a cure like this, who needs to be sick?

No one has ever said of a web site "Man, their data sure is pure!" They compliment what is being done with the data. I don't recommend you keep your hands tied behind your back on this one. I guarantee your competitors aren't.