I have 3 subqueries that work fine individually. I am attempting to join them with Union All. The query runs and gives the correct number of results but the format of the date field is messed up; for example '2016-11-01' is showing as '2,016, -11, -01'. This is the same for every row.
I've had a search and have found some threads concerning aliases but they do not seem to be the problem here as the query is running fine, with the exception of the date format.
Any help would be appreciated, thanks.
Here is the code:
# offers_8a
SELECT advisor, ats_id, offer_date
FROM max_offers
WHERE offer_date > (CURDATE()- INTERVAL 135 DAY)
GROUP BY advisor, ats_id, offer_date
UNION ALL
# offers_8b
SELECT mo.advisor, mo.ats_id, mo.offer_date
FROM max_offers as mo
INNER JOIN source_tables.ats as ats
ON mo.ats_id = ats.ats_id
INNER JOIN source_tables.intake_corrections as ic
ON ats.intake = ic.sugar_name
INNER JOIN source_tables.intakes as it
ON ic.intake = it.intake
INNER JOIN source_tables.dates as dt
ON ic.intake = dt.current_intake
GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start
HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY)
UNION ALL
# offers_8c
SELECT mo.advisor, mo.ats_id, mo.offer_date
FROM max_offers as mo
INNER JOIN source_tables.ats as ats
ON mo.ats_id = ats.ats_id
INNER JOIN source_tables.intake_corrections as ic
ON ats.intake = ic.sugar_name
INNER JOIN source_tables.intakes as it
ON ic.intake = it.intake
INNER JOIN
#s_prev_intake
(SELECT current_next_intakes.current_intake, assign, forecast_intake, forecast_intake_sugar
FROM source_tables.dates
INNER JOIN source_tables.current_next_intakes
ON source_tables.dates.current_intake = source_tables.current_next_intakes.current_intake
WHERE assign = 'Prev') AS prv
ON ic.intake = prv.forecast_intake
GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start
HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY);
UNION
is not the villain.