Temporary tables in mySQL

[rrshanks]

Hi,
 Is it possible to create a temporary table in mySQL using columns from tables in 
two databases? Basically I want to split a table between two databases and if some 
logic is satisfied I want to merge(the right word?) the 
two tables into one in one of these databases. What's the best way to achieve this?
 I am using mySQL 4.0.15. Any   information/help/suggestions or pointers would be 
much appreciated.

Thanks

-Ravi 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables in mySQL

[Rhino]

It's dead easy; see the example below. By the way, the term you want is
'join', not 'merge'.

In the example, Sample is the name of the database. 'emp' and 'dept' are two
tables that can be joined on a common value; the common value is called
'workdept' in the 'emp' table and 'deptno' in the 'dept' table.

Here are the table layouts:

+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| deptno   | char(3) |  | PRI | |   |
| deptname | varchar(36) |  | | |   |
| mgrno| varchar(6)  | YES  | | NULL|   |
+--+-+--+-+-+---+

+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| empno| char(6)  |  | PRI | |   |
| firstnme | char(12) |  | | |   |
| midinit  | char(1)  | YES  | | NULL|   |
| lastname | char(15) |  | | |   |
| workdept | char(3)  |  | MUL | |   |
| salary   | decimal(9,2) |  | | 0.00|   |
+--+--+--+-+-+---+

Here is the sample script, written in bash, that demonstrates what you want
to do:

use Sample;

select Create temporary table containing join result as Comment;
drop table if exists join_temp;
create temporary table if not exists join_temp
select *
from dept as d inner join emp as e
on e.workdept = d.deptno;

select Display temporary table as Comment;
select * from join_temp;

Rhino

- Original Message - 
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, March 15, 2004 3:31 PM
Subject: Temporary tables in mySQL


 Hi,
  Is it possible to create a temporary table in mySQL using columns
from tables in two databases? Basically I want to split a table between two
databases and if some logic is satisfied I want to merge(the right word?)
the
 two tables into one in one of these databases. What's the best way to
achieve this?
  I am using mySQL 4.0.15. Any   information/help/suggestions or
pointers would be much appreciated.

 Thanks

 -Ravi


 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Self Join on temporary tables in MySQL 3.xx

[Ciprian Trofin]

A understand that a problem with temporary tables is that you can't
do a self-join in MySQL 3.xx using temporary tables. I have a situation
that requires exactly this (the alternative is not algorithm-effective).

I wonder if the following is a practical solution: to create a copy of the
temporary table and use it together with the original temporary table to
execute the join ?

--
 Ciprian

 Ambition is a poor excuse for not having enough sense to be lazy.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php