Using column as table join with concat

Question

I've been trying to join another table with the table name matching a value in a column from another table.

If I hard-code the table name to a matching table like __gun it works fine but I cannot get the col value to be used + concat underscores to the beginning.

The problem lies in the left join here:

left join CONCAT('__', b.related_table) c on b.related_id = c.id

I need the related_table column to be used in the join. With __ before it.

Attempts:

        SELECT a.*, c.*, b.equipable, b.related_table FROM inventory a
        inner join items b on a.item_id = b.id
        left join CONCAT('__', b.related_table) c on b.related_id = c.id
        WHERE 1=1
        and a.id = :inventory_id
        and a.user_id = :user_id

        SELECT a.*, c.*, b.equipable, b.related_table FROM inventory a
        inner join items b on a.item_id = b.id
        left join '__'+b.related_table c on b.related_id = c.id
        WHERE 1=1
        and a.id = :inventory_id
        and a.user_id = :user_id

        SELECT a.*, c.*, b.equipable, b.related_table FROM inventory a
        inner join items b on a.item_id = b.id
        left join "__"+Cast(b.related_table as nvarchar(4000)) c on b.related_id = c.id
        WHERE 1=1
        and a.id = :inventory_id
        and a.user_id = :user_id

Thank you for considering my question

FROM takes table name literals or subselects. It seems likely that this is a poor design. [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) — philipxy, Jan 10 '19 at 05:57

score 0 · Answer 1 · answered Jan 10 '19 at 04:38

0

You cannot construct a table name.

Plan A: Rethink the schema. It is usually bad design to have multiple 'identical' tables.

Plan B: Write a Stored Procedure that uses CONCAT, PREPARE, EXECUTE, and DEALLOCATE_PREPARE to build and run the query.

answered Jan 10 '19 at 04:38

Rick James

135,179
13
127
222

score 0 · Answer 2 · answered Jan 10 '19 at 04:53

You can do this using Prepared Statements or you can say dynamic SQL. Follow these steps :

Use SET for table name.
Use CONCAT to get the desired table name.
PREPARE statement for table created at step 1.
EXECUTE statement.
DEALLOCATE PREPARE to release the prepared statement.

Using column as table join with concat

2 Answers2