UNION equivilent required for 3.23.37

[Andy Hall]

Hi,

I have looked for answers on the net but havent managed to apply the
suggestions to my example; I would appreciate any help!

I have the following set up:

root_table (root_table_id, table_one_id, table_two_id, date)

table_one (table_one_id, col1)

table_two (table_two_id, col2)

I want to use one query to join root_table with both the other tables,
getting "col1" out if root_table.table_one_id is not NULL and "col2" out if
root_table.table_two_id is not NULL. I need to then ORDER BY root_table.date

With a union, I would have:

(SELECT root_table.col1, date
FROM root_table INNER JOIN table_one
    ON root_table.table_one_id = table_one.table_one_id)
UNION
(SELECT root_table.col2, date
FROM root_table INNER JOIN table_two
    ON root_table.table_two_id = table_two.table_two_id)
ORDER BY date

But I cant do UNION's in MySQL, so how can I do this?

Any help appreciated,

Andy Hall.