So what I'm trying to do is create a temporary table with a unioned set of results that have distinct conditions. The information is largely the same, just in different places that need to be matched.
My query is below;
DROP TABLE IF EXISTS temp_data; CREATE TEMPORARY TABLE IF NOT EXISTS temp_data AS (SELECT * FROM ((SELECT src.value AS 'sr_code', DATE(rd.value) AS 'release_date', sw.value AS 'songwriter', SEC_TO_TIME(rt.value) AS 'runtime', COALESCE(track.original, 0) AS 'original', COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc' FROM catalog_product_entity prod LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249 LEFT JOIN catalog_product_entity_varchar src ON src.row_id = prod.row_id AND src.store_id = 0 AND src.attribute_id = 218 LEFT JOIN catalog_product_entity_varchar sw ON sw.row_id = prod.row_id AND sw.store_id = 0 AND sw.attribute_id = 308 LEFT JOIN catalog_product_entity_int rt ON rt.row_id = prod.row_id AND rt.store_id = 0 AND rt.attribute_id = 226 LEFT JOIN downloadable_link link ON prod.row_id = link.product_id LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id WHERE prod.attribute_set_id = 13 AND src.value IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0) UNION ALL (SELECT track.sr_code AS 'sr_code', DATE(rd.value) AS 'release_date', track.songwriter AS 'songwriter', SEC_TO_TIME(track.runtime) AS 'runtime', COALESCE(track.original, 0) AS 'original', COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc' FROM catalog_product_entity prod LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249 LEFT JOIN downloadable_link link ON link.product_id = prod.row_id LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id LEFT JOIN downloadable_link_title title ON title.link_id = link.link_id WHERE prod.attribute_set_id = 12 AND track.sr_code IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0)) AS 'temp_data_table');
But when I run the query, I'm returned with a syntax error. At first, I was just running the union selects as the create statement, which I learned you cannot do. I wrapped it in another select to use that statement as a subquery (as indicated here - https://stackoverflow.com/a/31751787/18582667). I'm able to get the SELECT + UNION + SELECT to run without the temp table syntax so my query itself seems to be fine.
This is what I receive:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp_data_table')' at line 31
I'm not sure what I did wrong and why it's throwing that at me. I'm leaning towards parens, but I don't have any non-paired ones in there.
I am working with a Magento 2 install, but I don't believe that's entirely relevant. Throwing it in there just to be thorough.
Running MySQL 5.6.42-84.2 Percona Server (GPL), Release 84.2, Revision 6b2b987 on Red Hat Enterprise Linux Server 7.9 (Maipo).