Below is the database base snippet of the database that I am currently working on.
Table transactions
:
id | reference_table | reference_field | reference_id | created_at |
---|---|---|---|---|
1 | online_recharges | online_recharge_id | 10 | 2024-01-31 |
2 | online_recharges | online_recharge_id | 11 | 2024-03-03 |
3 | wallet_deductions | wallet_deduction_id | 10 | 2024-02-01 |
… | … | … | … |
Table online_recharges
:
online_recharge_id | remarks | amount | … |
---|---|---|---|
10 | online recharge on 1st January | 100 | … |
11 | online recharge on 3rd March | 150 | … |
Table wallet_deductions
:
wllet_deduction_id | remarks | amount | … |
---|---|---|---|
10 | wallet deduction on 1st February | 20 | … |
I need to join the tables based on the transactions.reference_table
‘s value. Something like this:
SELECT T.reference_table, ref.remarks, ref.amount, T.created_at FROM transactions AS T
LEFT JOIN T.reference_table AS ref ON ref.reference_field = T.reference_id
ORDER BY T.created_at DESC
Is is possible..?
2
Answers
Creating a dynamic SQL join that relies on table and field names stored in another table presents a complex and non-standard challenge in SQL. Standard SQL queries do not inherently support dynamic table or column names within JOIN clauses or other query segments, as the execution plan requires static details for compilation and optimisation. Nonetheless, there are alternative techniques and workarounds that can be employed to obtain equivalent outcomes. You can make use of conditional joins:
For a fixed number of tables you can obtain the desired result like this.
Note that this kind of join is not well supported by indexes and therefore will cause performance problems with large tables.
Schema (MySQL v8.0)
View on DB Fiddle