Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: join tables based on table name as a column value

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..?

Tags:

2

Answers


  1. 0

    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:

    SELECT 
    T.reference_table, 
    CASE 
        WHEN T.reference_table = 'online_recharges' THEN Recharge.remarks
        WHEN T.reference_table = 'wallet_deductions' THEN Deduction.remarks
    END AS remarks,
    CASE 
        WHEN T.reference_table = 'online_recharges' THEN Recharge.amount
        WHEN T.reference_table = 'wallet_deductions' THEN Deduction.amount
    END AS amount,
    T.created_at
FROM 
    transactions AS T
LEFT JOIN 
    online_recharges AS Recharge ON T.reference_table = 'online_recharges' AND Recharge.online_recharge_id = T.reference_id
LEFT JOIN 
    wallet_deductions AS Deduction ON T.reference_table = 'wallet_deductions' AND Deduction.wallet_deduction_id = T.reference_id
ORDER BY 
    T.created_at DESC
LIMIT 25;
    Login or Signup to reply.
  2. 0

    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.

    select * 
from transactions t join
(
    select 'online_recharges' as src, online_recharge_id as id, remarks, amount
    from online_recharges
    union all
    select 'wallet_deductions' as src, wallet_deduction_id as id, remarks, amount
    from wallet_deductions
) rac
on t.reference_table = rac.src
and t.reference_id = rac.id;
    id reference_table reference_field reference_id src id remarks amount
    1 online_recharges online_recharge_id 10 online_recharges 10 online recharge on 1st January 100
    2 online_recharges online_recharge_id 11 online_recharges 11 online recharge on 3rd March 150
    3 wallet_deductions wallet_deduction_id 10 wallet_deductions 10 wallet deduction on 1st February 20

    Schema (MySQL v8.0)

    create table transactions
(
  id integer,
  reference_table varchar(30),
  reference_field varchar(30),
  reference_id integer
 );
 
 create table online_recharges
(
  online_recharge_id integer,
  remarks varchar(200),
  amount integer
 );
 
 
 create table wallet_deductions
(
  wallet_deduction_id integer,
  remarks varchar(200),
  amount integer
 );
 
 insert into transactions values
 (1,    'online_recharges','online_recharge_id',    10),    
(2, 'online_recharges','online_recharge_id',    11),    
(3, 'wallet_deductions', 'wallet_deduction_id', 10);


 insert into online_recharges values
 (10,   'online recharge on 1st January',   100),   
(11,    'online recharge on 3rd March', 150);

 insert into wallet_deductions values
 (10,   'wallet deduction on 1st February', 20);

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search