Posted by nullGumby on 05/14/07 14:29

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?

• Next in forum: Want to make ''Get Latest Status'' function more efficient
• Prev in forum: Re: Run same query for each day across a date range?
• Thread view: UNION SELECT from Multiple Subselects

[Reply to this message]