1

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?

Community
  • 1
  • 1
augustin
  • 14,373
  • 13
  • 66
  • 79
  • 2
    This is a situation where dynamic SQL is the way to go. – Gabe Nov 12 '10 at 12:55
  • @Gabe: Note that I work with mysql. Is this a vendor-specific extension? – augustin Nov 12 '10 at 12:59
  • 3
    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. – Peter Lang Nov 12 '10 at 13:08
  • @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. – augustin Nov 12 '10 at 13:23
  • 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. – HLGEM Nov 12 '10 at 18:12

2 Answers2

2

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).
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
oezi
  • 51,017
  • 10
  • 98
  • 115
  • Thanks. How would you translate my query into the 2 sql statements you speak of? – augustin Nov 12 '10 at 13:31
  • 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. – eaolson Jun 01 '11 at 01:50
  • 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. – Joe Apr 11 '18 at 15:34
0

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

Joe
  • 1,091
  • 1
  • 11
  • 23