SQL: How can we make a table1 JOIN table2 ON a table given in a field in table1?

Question

Imagine I have table1 which has a column named 'table_name'. I use table1.table_name to store the name of another table in the database. The referenceable tables would all have a field 'target_id.

Is is possible to use table_name in a JOIN statement?

For example:

SELECT t1.*, t2.* FROM table1 AS t1
                  JOIN table1.table_name AS t2 ON t1.table1_id = t2.target_id

The obvious solution is to use the script (C++ in my case) to get the table name first, and construct a SQL query from it. The question is: can we bypass the script and do this directly in SQL (MySQL)?

Edit: What is dynamic SQL?

Antilebanon

This is a situation where dynamic SQL is the way to go. – Ayn

@Gabe: Note that I work with mysql. Is this a vendor-specific extension? – Antilebanon

This is most likely a situation where the model should be changed. You could try to describe what you are accomplishing with this column and we might be able to help you improve your model. – Divider

@Peter Lang, thanks. I am precisely in the middle of defining my model. I am still uncertain which way to go and I am weighing different options, hence this question. – Antilebanon

I would suggest that your model is wrong if you need to do this. WHy do you need to refernce different tables? Please show the table structures you are proposing and perhaps we can help you to a better design structure. – Datum

Answer 1

The only chance you have is to do 2 SQL statements:

• select the tablename you need
• use this table-name to dynamically build the secound query to get the data you need - what you want isn't possible to do with SQL directly (and it sounds like you've designed your database wrong in some way - but that's hard to say without knowing what's the goal of it).

Clarindaclarine

Thanks. How would you translate my query into the 2 sql statements you speak of? – Antilebanon

Basically, you need to build a string and then run that as a SQL query, which you can't do from inside SQL. You need something that sits outside of SQL do run the query. That could be something as simple as the mysql client. – Lamberto

This doesn't sound like an elegant solution. From what I can tell from the OP's question, each row could have a different table to join to. – Dispend

Answer 2

I know I'm late to the party, but I wanted to offer a different solution. I see this sort of thing a lot in audit tables. The column table_name would refer to "what table was changed" and table1_id would refer to the ID of the row that changed in that table. In this case, the audit table is pointing back to many different tables that don't normally get joined.

Here goes:

SELECT t1.*, t2.*, t3.*, t4.*, t5.*

FROM table1 AS t1

left JOIN table2 AS t2 
    ON t1.table1_id = t2.target_id
    and t1.table_name = 'table2'

left JOIN table3 AS t3
    ON t1.table1_id = t3.target_id
    and t1.table_name = 'table3'

left JOIN table4 AS t4 
    ON t1.table1_id = t4.target_id
    and t1.table_name = 'table4'

left JOIN table5 AS t5 
    ON t1.table1_id = t5.target_id
    and t1.table_name = 'table5'

Of course, the main drawback is that each table that can be possibly referenced needs to be explicitly included in the SQL command.

You can get more elegant output using this as your select list:

SELECT 
     t1.*, 
     coalesce(t2.fieldA, t3.fieldA, t4.fieldA, t5.fieldA) as fieldA,
     coalesce(t2.fieldB, t3.fieldB, t4.fieldB, t5.fieldB) as fieldB

etc

Dispend