RE: How to build a single temporary table from 3 tables on the fly

2006-10-11 Thread Kerry Frater 
Thanks Phil

It gives me a solution and some reading.

Kerry
  -Original Message-
  From: Philip Mather [mailto:[EMAIL PROTECTED]
  Sent: 11 October 2006 10:02
  To: [EMAIL PROTECTED]
  Cc: mysql@lists.mysql.com
  Subject: Re: How to build a single temporary table from 3 tables on the
fly


  Kerry,

How do I select from 3 tables into a single table (consequtive rows not
joined ones) and include a two new columns which is the name of the table
from which the data has been extracted, and a fixed piece of text.
  I'd do something like...

  CREATE TABLE Merged_names
  (
 Temp table definition goes here
  ) ENGINE=MEMORY
  SELECT * FROM
  (
 (
SELECT
   main, hardcodedtablename1, `surname`
FROM
   table1
WHERE
   someCriteria = someOtherCriteria
 )
 UNION ALL
 (
SELECT
   main, hardcodedtablename2, `surname`
FROM
   table2
WHERE
   someCriteria = someOtherCriteria
 )
 UNION ALL
 (
SELECT
   main, hardcodedtablename3, `surname`
FROM
   table3
WHERE
   someCriteria = someOtherCriteria
 )
  ) AS TMP;


This “select” can be executed by more than one person at a time, so I need
to extract into a transient temp table so that I can view the dataset.

e.g. I have 3 tables containing names and want to extract the rows of a
particular surname so I would have let’s say
select ‘main’, thetablename, surname from t1 into myautogentable
select ‘personal’, thetablename, surname from t2 into myautogentable
select ‘group’, thetablename, surname from t3 into myautogentable
  Does that do roughly what you needed?  I'd suggest reading...
  http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
  http://dev.mysql.com/doc/refman/5.0/en/create-table.html, search for and
start reading at CREATE TABLE new_tbl SELECT
  http://dev.mysql.com/doc/refman/5.0/en/union.html

  Regards,
  Phil

Re: How to build a single temporary table from 3 tables on the fly

2006-10-11 Thread Philip Mather 

Kerry,

It gives me a solution and some reading.
  
No probs, here's some actual code that I hacked together on a 
4.1-sommat-or-other database, an important thing to note is to be 
careful of any Unique keys selected from the three individual tables as 
they may no longer be unique of course once you've union them using the 
ALL method.


CREATE TABLE Merged_names
(
  `ID` int(11) unsigned NOT NULL auto_increment,
  `Static_field` varchar(50) NOT NULL default '',
  `Parent_table` varchar(255) NOT NULL default '',
  `First_name` varchar(50) NOT NULL default '',
  `Last_name` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MEMORY
SELECT Static_field, Parent_table, First_name, Last_name FROM
(
  (
 SELECT
ID, Static Text AS Static_field, staff1 AS Parent_table, 
First_name, Last_name

 FROM
staff1
 LIMIT 10, 5
  )
  UNION ALL
  (
 SELECT
ID, Static Text AS Static_field, staff2 AS Parent_table, 
First_name, Last_name

 FROM
staff2
 LIMIT 20, 5
  )
  UNION ALL
  (
 SELECT
ID, Static Text AS Static_field, staff3 AS Parent_table, 
First_name, Last_name

 FROM
staff3
 LIMIT 30, 5
  )
) AS TMP;

Regards,
   Phil


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