Parameterized table name

Question

this is my problem: I want to check rows in a table which name is parameterized, something like table_X. The value of X comes from another table, so for example in my main table I have a column c_id and a column X, the table to join has name table_X, it EXISTS with no doubt, and it has the same column c_id, which I shall join on, to check if there are values of c_id in that table.

1. I've tried a view, but without success, because I can't put a parameterized table name in a view. I can parameterize where clauses and other things, but no table names.

2. I've tried a procedure, with

   SET @q = CONCAT('select blabla from table_', X);
   PREPARE stmt FROM @q;
   EXECUTE stmt;
   

   but procedures can't return values, and I need it, because I need to know if there is the c_id value in the parameterized table, else it is useless.

3. I've tried a function, but "Dynamic SQL is not allowed in stored function or trigger"

So what can I do to extract this data? I'm calling this view/function/whatever from PHP, and I know I can do it from PHP side, with two queries, but I need to do it db-side, for future implementations. Is it possible?

NOTE: I can't modify the structure of the DB :) btw, it's the Limesurvey db, sounds like a crazy db structure, huh?

Answer 1

Why do you need separate tables like this? It's usually a sign of bad design. Wouldn't it just be easier to create a single table with an identifier field for whichever X` value that record belongs to, that you can join/filter on?

That'd reduce the query to something like

SELECT ...
FROM othertable
JOIN bigtable ON othertable.c_id = bigtable.c_id AND othertable.fieldName = bigtable.fieldName

Answer 2

The only way, without dynamically building queries, is to hard code in every combination and pick out the one you want.

If the table name is a parameter to a stored procedure, this can be in IF blocks. But it feels clunky.

If the fields from each table are the same, you can union the tables together and select from those...

CREATE VIEW myUnifiedStructure AS
      SELECT 'Table1' AS tableName, * FROM Table1
UNION SELECT 'Table2' AS tableName, * FROM Table2
UNION SELECT 'Table3' AS tableName, * FROM Table3
-- etc

SELECT * FROM myUnifiedStructure WHERE tableName = 'Table1'

If the fields are different in each table, you may only be interested in a subset of the fields...

CREATE VIEW myUnifiedStructure AS
      SELECT 'Table1' AS tableName, field1 AS field1, field4 AS field2 FROM Table1
UNION SELECT 'Table2' AS tableName, field2 AS field1, field3 AS field2 FROM Table2
UNION SELECT 'Table3' AS tableName, field2 AS field1, field4 AS field2 FROM Table3
-- etc

Or you can pass in NULLs for fields that don't exist in the source table...

CREATE VIEW myUnifiedStructure AS
      SELECT 'Table1' AS tableName, NULL   AS field1, field2 AS field2 FROM Table1
UNION SELECT 'Table2' AS tableName, field1 AS field1, field2 AS field2 FROM Table2
UNION SELECT 'Table3' AS tableName, field1 AS field1, NULL   AS field2 FROM Table3
-- etc