Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Merge table

  1. #1
    Join Date
    Apr 2004
    Posts
    9

    Unanswered: Merge table

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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.

  3. #3
    Join Date
    Apr 2004
    Posts
    9
    I am using MySQL and I believe MySQL does not allow UNIONs

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by rageous
    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!

  5. #5
    Join Date
    Apr 2004
    Posts
    9
    Correction. MySQl does support UNIONs. What is doesnt support is VIEWs

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No views in mySQL? Good grief...!

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select sum(column), etc
    from
    (
    inline view
    )
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    Is that really so? I thought it was supposed to be a serious contender in the RDBMS arena!
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2004
    Posts
    9
    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).

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Now is there a way to achieve this result without doing a merge or a join"

    no

    needs a UNION

    Code:
    select foo, bar
      from (
    select foo, bar from EveryWeek
    union
    select foo, bar from EveryWeek1
    union
    select foo, bar from EveryWeek2 
    ) as inlineview
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2004
    Posts
    164
    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!

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    i would love to go with oracle

    zip it up and email it to me, would you please?
    Your mail server would choke!

    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Your mail server would choke!
    yes, i was making a point

    you might also have said that, absent a license fee, it would be illegal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •