From: Date: January 28 2004 5:02pm
Subject: UNION equivilent required for 3.23.37
List-Archive: http://lists.mysql.com/mysql/158544
Message-Id: <001b01c3e5b8$30f8a2d0$05a8a8c0@OXYGEN>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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.