I have multiple tables EveryWeek, EveryWeek1, EveryWeek2 etc which are all similar. I am trying to do a merge on these tables so I can query the merged table to get the sum, avg etc.
The way to do it I find on the web is this:
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2);

I have 2 problems with this (I dont know if these 2 problems can be avoided)
1. This creates a new table which I dont prefer (if there is another way).
2. This seems to force me to create the table, total, with all the columns in table t1 and t2. I need to create the table only with 2 out of 6 columns

I need my solution to work in both MySQL and Oracle

Thanks

Why not create a view (even a materialized) with UNIONs to merge the tables (selecting the 2 columns from each table), then use the view in your select statement which you would use sum, avg, etc.

I am using MySQL and I believe MySQL does not allow UNIONs

Is that really so? I thought it was supposed to be a serious contender in the RDBMS arena!

Correction. MySQl does support UNIONs. What is doesnt support is VIEWs

No views in mySQL? Good grief...!

select sum(column), etc
from
(
inline view
)

now, now, tony, be nice

mysql actually is a serious contender

how many mysql installations should've gone to oracle but didn't?

whatever could the reason have been?



r123456, your inline view works only in mysql 4.1 (or should -- i can't test 4.1, seeing as how it's still "alpha" code and my host won't install it)

Let me rephrase my problem and explain my basic need.
I have multiple tables EveryWeek1, EveryWeek2, EveryWeek3 etc and all these tables have the same column structure. These tables contain records split over a period of time. When the user requests a top 10 between time A and time B, the records for this time interval could span across multiple tables (say EveryWeek1 and EveryWeek2). The number of records in these tables is roughly 500,000 per table.
Now I need to find the top 10 (or top 100) within time interval A and B. I hate to create a table/view of the entire tables that spans across A and B in order to get just 10 or 100 records.
Now is there a way to achieve this result without doing a merge or a join. This is on Java (though Id prefer to do this in at the DB level).

"Now is there a way to achieve this result without doing a merge or a join"

no

needs a UNION

You can write a stored procedure (or does MySQL not support those either?) where you could dynamically prepare a SELECT statement concatenate the date range you want to pull data. The results would then have to be inserted into a temporary table (MySQL support those?). You could even make use of the system tables to loop through pulling the name of EveryWeek.. and concatenating them within your SELECT statement.

ie. 'SELECT a, b FROM ' || systemtable.tablename || FROM systemtable WHERE tablename like 'EveryWeek%'

Quite frankly, if you take MySQL out of the mix and go with Oracle, you would have had the information you need in a fraction of the time it has taken for suggestions to be posted/replied.

no, mysql does not support stored procs

yes, mysql supports temp tables

i would love to go with oracle

zip it up and email it to me, would you please?

This is how you can tie in all your tables.

select column1, column2
into table1
from Everyweek
union
select column1, column2
from
Everyweek1
union
select column1, column2
from
Everyweek3

just specify the column that you want to choose.

I hope this hepls!

Your mail server would choke!

-PatP

yes, i was making a point

you might also have said that, absent a license fee, it would be illegal