Thanks Bill; the first example did exactly what I wanted. I didnt need the "where" clause though; obviously didnt explain myself clearly!
I am sure I tried something similar to that, but I am used to MS SQL 7 and started sticking brackets everywhere which must have caused the errors I was getting... Thanks again > 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]
