Jump to content


Photo

MySQL Full Join


  • Please log in to reply
3 replies to this topic

#1 luxline

luxline
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationEurope

Posted 25 January 2003 - 02:12 PM

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.

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 27 January 2003 - 10:55 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 bofh5255597

bofh5255597
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationAustralia, Canberra

Posted 27 January 2003 - 03:43 PM

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?

#4 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 28 January 2003 - 12:26 PM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users