1

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;
  • If you simply execute SELECT * FROM enrols_new it has records? – Arulkumar Sep 15 '17 at 11:24
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.

  • Plan A worked. Not sure why I put that Group By in there... Bit of a silly mistake. Thanks Rick, much appreciated – agwah Sep 15 '17 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.