Union 2 Temp Tables

Question

I am trying to create a union of 2 temp tables, but the query is only returning values from the first table.

From searching the only thing I can find is that you cannot refer to the same temp table more than once in a query, but I am not doing this.Both temp tables are based on the same source table, but they are separate temp tables, which both run fine when ran individually:

EDIT - FURTHER DETAILS: It is the records from enrols_new that do not appear in the union, however if I union enrols_new with another temp table derived from a different source table it works ok. So it does seem it is something to do with both temp_tables being based on the same source table

DROP TEMPORARY TABLE IF EXISTS apps_new;
CREATE TEMPORARY TABLE apps_new AS

SELECT      bi_data.t_sugar_apps.ClientID, 'Application' AS `type`, 
            MIN(bi_data.t_sugar_apps.date_created) AS date_created, 
            bi_data.t_sugar_apps.adm_score, bi_data.t_sugar_apps.delivery,
            'Application' AS lead_source

FROM            bi_data.t_sugar_apps
                INNER JOIN arden_ga_job.arden_topline
                ON bi_data.t_sugar_apps.ClientID = arden_ga_job.arden_topline.ClientID

WHERE           bi_data.t_sugar_apps.region = 'UK' AND 
                arden_ga_job.arden_topline.RowDate <= bi_data.t_sugar_apps.date_created

GROUP BY        bi_data.t_sugar_apps.ClientID, `type`, 
                bi_data.t_sugar_apps.adm_score, 
                bi_data.t_sugar_apps.delivery,
                bi_data.t_sugar_apps.lead_source;



DROP TEMPORARY TABLE IF EXISTS enrols_new;
CREATE TEMPORARY TABLE enrols_new AS

SELECT      bi_data.t_sugar_apps.ClientID, 'Enrol' AS `type`, 
            MIN(bi_data.t_sugar_apps.date_enrolled) AS date_created, 
            NULL AS adm_score, bi_data.t_sugar_apps.delivery,
            'Enrol' AS lead_source

FROM            bi_data.t_sugar_apps
            INNER JOIN arden_ga_job.arden_topline
                ON bi_data.t_sugar_apps.ClientID = arden_ga_job.arden_topline.ClientID

WHERE           bi_data.t_sugar_apps.date_enrolled IS NOT NULL AND bi_data.t_sugar_apps.region = 'UK'
            AND arden_ga_job.arden_topline.RowDate <= bi_data.t_sugar_apps.date_created

GROUP BY        bi_data.t_sugar_apps.ClientID, `type`, bi_data.t_sugar_apps.adm_score, bi_data.t_sugar_apps.delivery,
            bi_data.t_sugar_apps.lead_source;



DROP TEMPORARY TABLE IF EXISTS  clients;
CREATE TEMPORARY TABLE clients AS

SELECT  ClientID, `type`, date_created, adm_score, delivery, lead_source
FROM        (
        SELECT  ClientID, `type`, date_created, adm_score, delivery, lead_source
        FROM        apps_new
        UNION ALL
        SELECT  ClientID, `type`, date_created, adm_score, delivery, lead_source
        FROM        enrols_new
        )            AS uni

GROUP BY    ClientID;

Answer 1

Plan A: Get rid of the GROUP BY ClientID

Plan B: Realize that the GROUP BY is ill-formed. When asking for one row per ClientID, well, which row? The App row? or the Enrol row?

Plan C: Use some form of aggregate, such as GROUP_CONCAT(type) for the various ambiguous columns.