Jump to content


Photo

[INFO:]How to do a FULL OUTER JOIN in Mysql 3.23


  • Please log in to reply
3 replies to this topic

#1 pallevillesen

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

Posted 28 January 2003 - 01:29 PM

I have two tables, a and b which contains the following:


A:

+----+

| id |

+----+

| a  |

| b  |

| c  |

+----+



B:

+----+

| id |

+----+

| b  |

| c  |

| d  |

+----+




And I would like to do a search returning ALL rows from both tables. I.e.

A
B
C
D

(Somebody might want something like

A NULL
B B
C C
NULL D

Which is easy, but this example is the simplest.


create temporary table t1unique as

select a.id from a LEFT OUTER JOIN b on a.id = b.id 

where b.id IS NULL;



This will get all rows with ONLY a value in table A.



create temporary table t2unique as

select b.id from a RIGHT OUTER JOIN b on a.id = b.id 

WHERE a.id IS NULL;



This will get all rows with ONLY a value in table B.



create temporary table t3 as

select a.id from a, b where a.id = b.id;



This will get all rows with matching values in A and B.



INSERT INTO t3 select * from t1unique;

INSERT INTO t3 select * from t2unique;



Putting the first two queries into the third one....



select * from t3 order by id;



Tadaa...



drop table t1unique;

drop table t2unique;

drop table t3;



Cleaning up.




Ugly code, I know - but it\'s working.


mysql> select * from t3 order by id;

+----+

| id |

+----+

| a  |

| b  |

| c  |

| d  |

+----+

4 rows in set (0.00 sec)


Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 28 January 2003 - 07:29 PM

:shock: you love those temporary\'s P :D
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 pallevillesen

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

Posted 28 January 2003 - 10:38 PM

Nope.... I actually quite dislike them, but I couldn\'t figure out how to this is one long ugly sql statement (ahem.. that is in mysql, where subselects and full outer joins are nonexistent (in version 3.34))...

And I was freaked out by the lack of results when googling for MYSQL FULL OUTER JOIN....

I think we could need a tutorial on temporary tables and what they are good for (i.e. subselects and outer joins tips and trick)... Anyone?

P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#4 Uranium-235

Uranium-235
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 29 January 2003 - 07:40 PM

Wow, now I need a drink ;)
Random Quote:[br][br][url=http://www.paintbug.com/scripts/quote_source.phps]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users