Jump to content

Archived

This topic is now archived and is closed to further replies.

luxline

MySQL Full Join

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.