Thanks to Rick I would go for a single table. I tried the following,
but hasn't been successful yet. First I created one 'master' table for
one country, with a country column (ctry) as Rick suggested, then I
tried to use the loop suggested by Johan to insert the records for
each of the remaining countries into the master table:
/*STEP 1. Create a master table with for one country */
CREATE TABLE `table_new`
SELECT var1, var2`
, (case when (country='US') then 1 else 0 end) AS citizen
,'US' AS ctry
FROM `USA_table`
LEFT JOIN
WORLD
USING(YEAR);
/*STEP 2. from the dos prompt?*/
for x in UK ZA IN CN;
do mysql -ujoe -p -e "INSERT INTO `table_new`
SELECT var1, var2`, (case when (country='${x}') then 1 else 0 end) AS citizen
,'${x}' AS ctry
FROM `'${x}'_table`
LEFT JOIN
WORLD
USING(YEAR)";
For some reasons step2 is not working.
I assume step 2 must be run from DOS prompt (my mysql runs on a
server, and I am using PuTTY). Any thoughts?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
