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

Question

Below is the 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 it possible..?

Answer 1

For one line of transaction you can use dynamic sql

s securtity risk in only present, if your content from, the transaction table isn't check properly, so hat ic doesn't have any sql injection code.

SELECT 
    CONCAT('SELECT "', reference_table,'", ref.remarks, ref.amount,"', t.created_at,
  '" FROM ', reference_table, ' as ref WHERE ' ,reference_field, ' = ?'),  reference_id
  INTO @sql,@id
FROM transactions t 
WHERE id = 1;
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @id;
DROP PREPARE stmt;

@sql
SELECT "online_recharges", ref.remarks, ref.amount,"2024-05-08" FROM online_recharges as ref WHERE online_recharge_id = ?

Statement prepared

online_recharges	remarks	amount	2024-05-08
online_recharges	online recharge on 1st January	100	2024-05-08

fiddle

To get mmultiple rows of tansactions you can do you need a loop, but this will cost time

CREATE PROCEDURE GetFilteredData()
BEGIN
  DECLARE bDone INT;

  DECLARE sql_select VARCHAR(2000);  
  DECLARE ref_id BIGINT;
  
  DECLARE curs CURSOR FOR  SELECT 
    CONCAT('INSERT INTO tblResults SELECT "', reference_table,'", ref.remarks, ref.amount,"', t.created_at,
  '" FROM ', reference_table, ' as ref WHERE ' ,reference_field, ' = ?'),  reference_id
FROM transactions t ORDEr BY id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  DROP TEMPORARY TABLE IF EXISTS tblResults;
  CREATE TEMPORARY TABLE IF NOT EXISTS tblResults  (
    reference_table varchar(100),
    remarks varchar(100),
    amount DeCIMAL(10,2),
    created_at date
  );

  OPEN curs;

  SET bDone = 0;
  label1: LOOP
    FETCH curs INTO sql_select,  ref_id;
   If bdone = 1 then
       LEAVE label1;
  END IF;
    set @sql = sql_select;
   SET @id = ref_id;
  PREPARE stmt FROM @sql;
   EXECUTE stmt USING @id;
   DROP PREPARE stmt;

  END LOOP label1;

  CLOSE curs;
  SELECT * FROM tblResults;
END

call GetFilteredData()

reference_table	remarks	amount	created_at
online_recharges	online recharge on 1st January	100.00	2024-05-08
online_recharges	online recharge on 3rd March	150.00	2024-05-08
wallet_deductions	wallet deduction on 1st February	20.00	2024-05-08

fiddle

Answer 2

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

Answer 3

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;