Pass column name dynamically for joining table in MySQL

Question

For the last few days, I have been working to write a MySQL query to extract a result. Basically, I have to join two tables log and conditions table based on any column of the log table from the trigger_param_value column. Here the column name of log table is present in the trigger_param_name column of the condition table.

I have already write a SQL query.

 select al.* from log as al
 inner join conditions as hc on hc.action=al.action 
   and al.<hc.trigger_param_name> = hc.trigger_param_value
 where al.date_time >= DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY)
  AND al.date_time < CURRENT_DATE and customer_id is not null
  group by al.customer_id;

The problem is occurring for the second condition of the inner joining al.<hc.trigger_param_name>= hc.trigger_param_value. <hc.trigger_param_name>contains the column name of the column name of the log table.

Note: The trigger_param_name column contains different different column name based on row.

How can I dynamically set the column name?

Please add table definitions, sample data and expected outcome as text...I suspect your approach is flawed and the db design is flawed. — P.Salmon
– P.Salmon, Commented Nov 23, 2023 at 8:54
Thank you for your effort. It is working fine although I have not provided the DB definition. — Rejaul Karim
– Rejaul Karim, Commented Nov 23, 2023 at 10:51

user1191247 · Accepted Answer · 2023-11-23 09:00:37Z

1

You can't. You will need to write out the separate join criteria for each of the columns:

SELECT al.*
FROM log AS al
INNER JOIN conditions AS hc
    ON hc.action = al.action
    AND (
        (hc.trigger_param_name = 'col1' AND al.col1 = hc.trigger_param_value) OR
        (hc.trigger_param_name = 'col2' AND al.col2 = hc.trigger_param_value) OR
        ...
    )
WHERE al.date_time >= DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY)
AND al.date_time < CURRENT_DATE
AND customer_id IS NOT NULL
GROUP BY al.customer_id;

This query should lead to a 1055 error, as customer_id is not a unique key within the log table. Without knowing the structure of the tables and expected outcome, it is not possible to suggest how this should be fixed.

Please read MySQL Handling of GROUP BY for more details on the importance of ONLY_FULL_GROUP_BY and writing deterministic queries.

edited Nov 23, 2023 at 9:00

answered Nov 23, 2023 at 8:36

user1191247

13k2 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

agengha · Accepted Answer · 2023-11-23 08:57:48Z

0

You cannot dynamically set the column name, but there are other ways to achieve what you want. One i can suggest is using case statement.

select al.* from log as al
inner join conditions as hc 
    on hc.action=al.action 
    and case hc.trigger_param_name
        when 'column1' then al.column1
        when 'column2' then al.column2
        # more column goes here
    end = hc.trigger_param_value
where al.date_time >= DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY)
AND al.date_time < CURRENT_DATE and customer_id is not null
group by al.customer_id;

I should mention while you cannot dynamically set the column name as filter, you can make entire query dynamic for your reference. But for your case, query above i provided should be enough.

edited Nov 23, 2023 at 8:57

answered Nov 23, 2023 at 8:50

agengha

1997 bronze badges

Collectives™ on Stack Overflow

Pass column name dynamically for joining table in MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related