Jump to content

MySQL Full Join


luxline

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.

Link to comment
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.

Link to comment
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?

Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.