Andy,
Does this scratch the itch?
select col1, col2, date
from root_table
left join table_one on root_table.table_one_id = table_one.table_one_id
left join table_two on root_table.table_two_id = table_two.table_two_id
where table_one.table_one_id is not null
and table_one.table_one_id is not null
order by date;
You get what your requested, but the col1 and col2 are in different result
columns.
If you know only one of them is non-null, you can use an IF() operator to
get
the result in one column.
Otherwise, you can't do it by joining those 3 tables. (For example, if each
of the 3 tables contained exactly one row, there would be no join with more
than one row.)
If you really want to, I think the following will work, using a "helper
table,"
which you could create once and keep around. It sure is ugly--union would
be
a lot nicer. Basically, the cross join on helper and root_table makes two
copies of root_table; you use these to form the two parts of your union
create temporary table helper (int which) type=heap;
insert into helper values (1), (2);
select if(which=1,col1,col2)
from helper, root_table
left join table_one on root_table.table_one_id = table_one.table_one_id
left join table_two on root_table.table_two_id = table_two.table_two_id
where which=1 and col1 is not null
or which=2 and col2 is not null
order by date, which;
From: "Andy Hall" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
> Subject: UNION equivilent required for 3.23.37
> Date: Wed, 28 Jan 2004 16:02:54 -0000
> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
