UNION SELECT from Multiple Subselects

Database

UNION SELECT from Multiple Subselects

I'm trying to get a UNION of UserIDs from multiple subselects. The original query--which puts all the found UserIDs into separate columns, looks like this:

--------------------------------------------------------------------------------
SELECT

tmp_MY_TABLE_2_3_4.LAST_UPDATED_USER_ID AS UserID_1
MY_DB_1.MY_TABLE_5.LAST_UPDATED_USER_ID AS UserID_2
MY_DB_1.MY_TABLE_6.LAST_UPDATED_USER_ID AS UserID_3

FROM

(
SELECT MY_DB_1.MY_TABLE_1.PRIMARY_KEY_ID
FROM MY_DB_1.MY_TABLE_1
WHERE MY_DB_1.MY_TABLE_1.PRIMARY_KEY_ID = 12345
) AS tmpMY_TABLE_1

LEFT JOIN
(
SELECT MY_DB_2.MY_TABLE_2.PRIMARY_KEY_ID
, MY_DB_2.MY_TABLE_2.LAST_UPDATED_USER_ID
FROM MY_DB_2.MY_TABLE_2
UNION
SELECT MY_DB_2.MY_TABLE_3.PRIMARY_KEY_ID
, MY_DB_2.MY_TABLE_3.LAST_UPDATED_USER_ID
FROM MY_DB_2.MY_TABLE_3
UNION
SELECT MY_DB_2.MY_TABLE_4.PRIMARY_KEY_ID
, MY_DB_2.MY_TABLE_4.LAST_UPDATED_USER_ID
FROM MY_DB_2.MY_TABLE_4
) AS tmp_MY_TABLE_2_3_4
ON tmpMY_TABLE_1.PRIMARY_KEY_ID = tmp_MY_TABLE_2_3_4.PRIMARY_KEY_ID

LEFT JOIN
MY_DB_1.MY_TABLE_5 ON tmpMY_TABLE_1.PRIMARY_KEY_ID = MY_TABLE_5.PRIMARY_KEY_ID

LEFT JOIN
MY_DB_1.MY_TABLE_6 ON tmpMY_TABLE_1.PRIMARY_KEY_ID = MY_TABLE_6.PRIMARY_KEY_ID
--------------------------------------------------------------------------------

As you can see, I'm getting "LAST_UPDATED_USER_ID" from all tables/aliases--but those tables/aliases need to LEFT JOIN to "tmpMY_TABLE_1.PRIMARY_KEY_ID", because I only care about "LAST_UPDATED_USER_ID"'s that are related to my "tmpMY_TABLE_1" records.

I tried putting, parenthesis around the whole SQL (aliasing as "tmpAllTables") and putting in the front:

--------------------------------------------------------------------------------
SELECT tmpAllTables.UserID_1 AS UserID FROM tmpAllTables
UNION
SELECT tmpAllTables.UserID_2 AS UserID FROM tmpAllTables
UNION
SELECT tmpAllTables.UserID_3 AS UserID FROM tmpAllTables
--------------------------------------------------------------------------------

But that didn't work. Any thoughts?
1 REPLY

Re: UNION SELECT from Multiple Subselects

Duh...I'm just going to create a temporary table, then do my second SELECT off of that.