I am trying to accomplish a full join with three temporary tables in MySql 5.7. I am emulating this (How to do a FULL OUTER JOIN in MySQL?) example but cannot find nor figure out how I'd accomplish that with three tables instead of two.
I've written:
##########################################################################################
#### QUERY GOALS: Number of Members Gained to X trigger & Number of Members Lost from X trigger
# to do
## add in parameters for ladders
##########################################################################################
#### DROP TABLE IF EXISTS
DROP TABLE IF EXISTS cte_leenk_ladder_history;
DROP TABLE IF EXISTS cte_ladder_history_self_join_lh1;
DROP TABLE IF EXISTS cte_ladder_history_self_join_lh2;
DROP TABLE IF EXISTS cte_ladder_history_join_lh1_lh2_current_trigger_record;
DROP TABLE IF EXISTS cte_distinct_completed_trigger_and_moved;
DROP TABLE IF EXISTS cte_completed_trigger_prior_to_date_no_change;
DROP TABLE IF EXISTS cte_completed_trigger_between_dates_no_change;
DROP TABLE IF EXISTS cte_completed_trigger_between_dates_then_change;
#set @ladder_value = 'ladder_advocacy';
#### CREATE PARAMETERS FOR LADDERS
CREATE TEMPORARY TABLE cte_leenk_ladder_history
SELECT
member_id
,ladder_change
,date_trigger_event
,@ladder_value
,lh.ladder_config_id as ladder_config_id
,trigger_name
,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
where lh.ladder_change = 1 and lc.active = 1 and lc.trigger = 1
limit 25000;
#### SET UP MASTER TABLE
create temporary table cte_ladder_history_self_join_lh1
select
member_id as member_id_lh1,
ladder_config_id as ladder_config_id_lh1,
trigger_name as trigger_name_lh1,
date_trigger_event as date_trigger_event_lh1,
ladder_name as ladder_name_lh1,
ladder_value as ladder_value_lh1,
ladder_change as ladder_change_lh1
from
cte_leenk_ladder_history as lh1;
create temporary table cte_ladder_history_self_join_lh2
select
member_id as member_id_lh2,
trigger_name as trigger_name_lh2,
date_trigger_event as date_trigger_event_lh2,
ladder_name as ladder_name_lh2,
ladder_value as ladder_value_lh2,
ladder_change as ladder_change_lh2
from
cte_leenk_ladder_history as lh2;
#### Current ladder history
create temporary table cte_ladder_history_join_lh1_lh2_current_trigger_record
select
cte_ladder_history_self_join_lh1.member_id_lh1,
cte_ladder_history_self_join_lh1.ladder_config_id_lh1,
cte_ladder_history_self_join_lh1.trigger_name_lh1,
cte_ladder_history_self_join_lh1.date_trigger_event_lh1,
cte_ladder_history_self_join_lh1.ladder_name_lh1,
cte_ladder_history_self_join_lh1.ladder_value_lh1,
cte_ladder_history_self_join_lh1.ladder_change_lh1,
cte_ladder_history_self_join_lh2.member_id_lh2,
cte_ladder_history_self_join_lh2.trigger_name_lh2,
cte_ladder_history_self_join_lh2.date_trigger_event_lh2,
cte_ladder_history_self_join_lh2.ladder_name_lh2,
cte_ladder_history_self_join_lh2.ladder_value_lh2,
cte_ladder_history_self_join_lh2.ladder_change_lh2,
@member_id:=member_id_lh2 AS member_id,
@row_number:=(CASE
WHEN @member_id = member_id_lh1
THEN @row_number + 1
ELSE 1
END) AS rank
from
cte_ladder_history_self_join_lh1 #previous or current ladder history
left join cte_ladder_history_self_join_lh2 on cte_ladder_history_self_join_lh1.member_id_lh1 = cte_ladder_history_self_join_lh2.member_id_lh2
and cte_ladder_history_self_join_lh2.ladder_name_lh2 = cte_ladder_history_self_join_lh1.ladder_name_lh1
and cte_ladder_history_self_join_lh1.ladder_value_lh1 <> cte_ladder_history_self_join_lh2.ladder_value_lh2
and cte_ladder_history_self_join_lh1.date_trigger_event_lh1 < cte_ladder_history_self_join_lh2.date_trigger_event_lh2 # lh2 shows second begin date / end date for lh if exists
where @row_number:=(CASE
WHEN @member_id = member_id_lh1
THEN @row_number + 1
ELSE 1
END) = 1
order by member_id_lh1 desc, date_trigger_event_lh2 desc;
#### count people who completed X trigger level prior to date and havent changed ever
create temporary table cte_completed_trigger_prior_to_date_no_change
select
cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1 as trigger_name__prior_to_date_no_change, #try removing trigger name grouping for ladder level counts
cte_ladder_history_join_lh1_lh2_current_trigger_record.ladder_value_lh1,
count(cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1) as count_trigger_prior_no_change
from cte_ladder_history_join_lh1_lh2_current_trigger_record
where date_trigger_event_lh1 < CAST('2018-01-01' AS DATE)
and member_id_lh2 is null
group by trigger_name_lh1, ladder_value_lh1
order by ladder_value_lh1 asc;
#### count people who completed X trigger level between dates that never became something else
create temporary table cte_completed_trigger_between_dates_no_change
select
cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1 as trigger_name__between_dates_no_change,
cte_ladder_history_join_lh1_lh2_current_trigger_record.ladder_value_lh1,
count(cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1) as count_trigger_between_no_change
from cte_ladder_history_join_lh1_lh2_current_trigger_record
where date_trigger_event_lh1 BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2018-12-01' AS DATE)
and member_id_lh2 is null
group by trigger_name_lh1, ladder_value_lh1
order by ladder_value_lh1 asc;
#### count people who completed X trigger level between dates AND became something else (people who moved on)
create temporary table cte_completed_trigger_between_dates_then_change
select
cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1 as trigger_name__between_dates_change,
cte_ladder_history_join_lh1_lh2_current_trigger_record.ladder_value_lh1,
count(cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1) as count_trigger_between_then_change
from cte_ladder_history_join_lh1_lh2_current_trigger_record
where date_trigger_event_lh1 BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2018-12-01' AS DATE)
and member_id_lh2 is not null
group by trigger_name_lh1, ladder_value_lh1
order by ladder_value_lh1 asc;
#### join the counts together - do this 3 times, for each table
/* t1 t2 */
select * from cte_completed_trigger_prior_to_date_no_change
left join cte_completed_trigger_between_dates_no_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change
union
select * from cte_completed_trigger_prior_to_date_no_change
right join cte_completed_trigger_between_dates_no_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change
/* t1 t3 */
select * from cte_completed_trigger_prior_to_date_no_change
left join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
union
select * from cte_completed_trigger_prior_to_date_no_change
right join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
/* t2 t3 */
select * from cte_completed_trigger_between_dates_no_change
left join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
union
select * from cte_completed_trigger_between_dates_no_change
right join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
the joins in question are at the end of the query. I'm getting an "error in syntax". I've also tried this approach (How to FULL OUTER JOIN multiple tables in MySQL), and this approach ((My)SQL full join with three tables)
Thoughts?
