MySQL coalesce non-null values conflicting with additional case when and coalesce statements - not sure how to fix

Question

I'm having trouble transforming code that I wrote to originally ignore null values to one that accepts and transforms null values into a 0.

The where clause removes any values where ladder_advocacy = null. We also leverage that in the case statement.

However, I actually do care about when ladder_advocacy (Etc) are null. When a person first joins the organization, their default ladder values are null. In my analysis, I want to know how many people moved from A à B, Bà E, etc as well as null -> B, null -> e. Right now, the query is severely undercounting any changes, because we know that some percentage of people actually don’t first join the org on a ladder level (say as a C1), instead first joining as a null.

I’m not sure how to work around this to fix it.

This is the original code:

CREATE TEMPORARY TABLE cte_leenk_ladder_history_order
     SELECT
        lh.member_id
        ,sc.salesforce_id
        ,sc.lastname
        ,trigger_record_id
        ,ladder_change
        ,date_trigger_event
        ,@ladder_value
        ,lh.ladder_config_id AS ladder_config_id
        ,trigger_name
        ,lc.active as config_active
        ,lc.`trigger` as trigger_active
        ,COALESCE(lh.ladder_advocacy,lh.ladder_elected,lh.ladder_policy,lh.ladder_organizing,lh.ladder_organizing,lh.ladder_engagement) AS ladder_value
        ,CASE WHEN lh.ladder_advocacy IS NOT NULL THEN 'ladder_advocacy'
               WHEN lh.ladder_elected IS NOT NULL THEN 'ladder_elected'
               WHEN lh.ladder_policy IS NOT NULL THEN 'ladder_policy'
               WHEN lh.ladder_organizing IS NOT NULL THEN 'ladder_organizing'
               WHEN lh.ladder_collective IS NOT NULL THEN 'ladder_collective'
               WHEN lh.ladder_engagement IS NOT NULL THEN 'ladder_engagement'
            END
            AS ladder_name
        FROM leeds_new.leenk_ladder_history AS lh
            INNER JOIN leeds_new.leenk_ladder_config AS lc ON lh.ladder_config_id = lc.ladder_config_id
            INNER JOIN leeds_new.salesforce_contacts AS sc ON lh.member_id = sc.salesforce_id
WHERE lh.ladder_advocacy IS NOT NULL
ORDER BY date_trigger_event  DESC;

I tried to do a coalese function to transform null –> 0 in a temp table before this first one and then joining it in:

CREATE TEMPORARY TABLE cte_ladder_levels_to_non_null
                SELECT
                 member_id,
                COALESCE(ladder_advocacy, 0) AS ladder_advocacy,
                COALESCE(ladder_elected, 0) AS ladder_elected,
                COALESCE(ladder_policy, 0) AS ladder_policy,
                COALESCE(ladder_organizing, 0) AS ladder_organizing,
                COALESCE(ladder_engagement, 0) AS ladder_engagement,
                COALESCE(ladder_collective, 0) AS ladder_collective
    FROM
         leeds_new.leenk_ladder_history;

CREATE TEMPORARY TABLE cte_leenk_ladder_history_order
     SELECT
        lh.member_id
        ,sc.salesforce_id
        ,sc.lastname
        ,trigger_record_id
        ,ladder_change
        ,date_trigger_event
        ,@ladder_value
        ,lh.ladder_config_id AS ladder_config_id
        ,trigger_name
        ,lc.active as config_active
        ,lc.`trigger` as trigger_active
        ,COALESCE(
                cte_ladder_levels_to_non_null.ladder_advocacy,
                cte_ladder_levels_to_non_null.ladder_elected,
                cte_ladder_levels_to_non_null.ladder_policy,
                cte_ladder_levels_to_non_null.ladder_organizing,
                cte_ladder_levels_to_non_null.ladder_organizing,
                cte_ladder_levels_to_non_null.ladder_engagement) AS ladder_value
        ,CASE WHEN lh.ladder_advocacy IS NOT NULL THEN 'ladder_advocacy'
               WHEN lh.ladder_elected IS NOT NULL THEN 'ladder_elected'
               WHEN lh.ladder_policy IS NOT NULL THEN 'ladder_policy'
              WHEN lh.ladder_organizing IS NOT NULL THEN 'ladder_organizing'
               WHEN lh.ladder_collective IS NOT NULL THEN 'ladder_collective'
               WHEN lh.ladder_engagement IS NOT NULL THEN 'ladder_engagement'
            END
            AS ladder_name
        FROM leeds_new.leenk_ladder_history AS lh
inner join cte_ladder_levels_to_non_null as llnn on lh.member_id = llnn.member_id
            INNER JOIN leeds_new.leenk_ladder_config AS lc ON lh.ladder_config_id = lc.ladder_config_id
            INNER JOIN leeds_new.salesforce_contacts AS sc ON lh.member_id = sc.salesforce_id
WHERE lh.ladder_advocacy IS NOT NULL
ORDER BY date_trigger_event  DESC;

But I’m getting an error “invalid field name” with this row: cte_ladder_levels_to_non_null.ladder_advocacy (and the ladder levels that follow it).

I also tried to remove the where clause, but that doesn’t solve the problem either, because it screws up the case.

Thoughts? Thanks!

Answer 1

Moved the ifnull into a latter part of the query (the last permanent table):

ifnull(member_id_lh2,0), 
            ifnull(trigger_name_lh2,0),
            ifnull(trigger_record_id_lh2,0), 
            date_trigger_event_lh2,
            ifnull(ladder_name_lh2,0),
            ifnull(ladder_value_lh2,0),
            ifnull(ladder_change_lh2,0),
            ifnull(config_active_lh2,0),
            ifnull(trigger_active_lh2,0),

Works perfectly.