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

Pass column name dynamically for joining table in MySQL

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 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.

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

Back To Top
Search