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

[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