MySQL Full Join

[luxline]

I have 2 tables

table 1

id

date (timestamp,6)

other infomation....

 

 

table 2

id

date (timestamp,6)

other infomation....

 

 

I want to know how to get ALL records from both tables, sorted by date.

 

MySQL doesnt appear to support full joins, so Im guessing I have to do something whacky to get around it.

 

I was thinking of just creating a new table, and dumping the values from both the other tables, but there must be an easier way.

 

All help most gratefully appreciated.

[pallevillesen]

select * from table1 LEFT OUTER JOIN table2 on table1.coloumnname = table2.coloumnname

 

This will insert NULL values where needed, i.e. missing rows from one of the two tables.

 

EDIT: OUPS! Just realised that IDs in table2 but missing in table1 won\'t be returned, sorry.... Will look at this later.

 

P.

[bofh5255597]

I too have something similar needed

 

I have accumulated soo much data which is required that I want to break up into yearly tables.

 

E.g. table2001, table2002 and table2003 etc

 

I can determine which table to include from a simple check but I need to work out how to join the relevant tables in a select statement

 

I would of expected a simple asumption by the mysql server when a select * from table2002,table2003 where ... would assume that it should append the data as long as all the fields are the same but I guessed wrong.

 

How do you get this to work?

[pallevillesen]

I just checked my notes about full join on mysql from earlier work.

 

It is rarely used and NOT implemented in mysql v. 3.23 BUT IT IS IN mysql 4.

 

I wrote that you may emulate a full join using \"two consequtive left joins, where one will be an empty query\" - now I\'m trying to figure out what I was thinking when I wrote this....

 

I think it is faster doing a dump into a temp table though, since two left joins on big tables will take some time.

 

That\'s my thoughts - if anybody comes up with a nice solution or figures out what I mean by two left joins - then please post it as a new thread under the header, \"How to do full joins in mysql\" - since this would be something many people request.

 

P.