Thanks Phil
It gives me a solution and some reading.
Kerry
-----Original Message-----
From: Philip Mather [mailto:philm@stripped]
Sent: 11 October 2006 10:02
To: kerry@stripped
Cc: mysql@stripped
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 lets 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
