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?

Re: UNION SELECT from Multiple Subselects

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

On May 14, 9:29 am, nullGumby wrote:
> 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:
>
> ------------------------------------------------------------ -------------=
--=AD-----
> 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 =3D 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 =3D tmp_MY_TABLE_2_3_4.PRIMARY_KEY_ID
>
> LEFT JOIN
> MY_DB_1.MY_TABLE_5 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =3D
> MY_TABLE_5.PRIMARY_KEY_ID
>
> LEFT JOIN
> MY_DB_1.MY_TABLE_6 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =3D
> MY_TABLE_6.PRIMARY_KEY_ID
> ------------------------------------------------------------ -------------=
--=AD-----
>
> 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:
>
> ------------------------------------------------------------ -------------=
--=AD-----
> 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
> ------------------------------------------------------------ -------------=
--=AD-----
>
> But that didn't work. Any thoughts?