Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    18

    Unanswered: Compare two tables and save it to a 3rd table

    Hey Guys,

    I have to compare two tables and save the matching result to a third table.
    I can't seem to save anything even thou i have the matching result displayed on the screen.

    Here is what I did :

    SELECT * FROM table1, table2
    -> WHERE table1.column2 = table2.column2
    -> ORDER BY column2;

    Then What?
    I tried JOIN / NATURAL JOIN / INJOIN ..
    Maybe I had an improper synthax .. neway nothing worked out so far I just ended up with a JOINT pain ..

    Things you should know:
    - Table 1, 2 and 3 have the exact same format.
    - Column2 is defined as an integer
    - Our DB exceed 3Gb..
    - Sorry if this is baby step .. im started mysql 2 weeks ago
    - Thanks Thanks Thanks

    Help please .......

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you say "save the matching result to a third table" what exactly does this mean?

    you say all three tables have the same format, so why are you saving the results of table1 AND table2? that would be twice as many columns!!!

    coould you please do a SHOW CREATE TABLE for these tables and explain exactly what columns you want saved

    in fact, please explain why you want any results saved at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    18
    Table 1 and 2 are filled up with numbers
    Table 3 is empty
    Each table has 5 columns of integers

    To create each table I just did this for each table :
    CREATE table1 (col1 int, col2 int, col3 int, col4 int, col5 int);

    What I need to have:
    If between table 1 and 2, the second column has the same number then I should copy the whole line (from column 1 to 5) into table 3.

    Sorry i copy pasted the wrong script, i had duplicated results at first so I changed it for :
    SELECT * FROM table1
    WHERE table1.col2 = table2.col2
    ORDER BY col2;

    but then How do I transfer this to table 3?...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    INSERT 
      INTO table3 
    SELECT table1.* 
      FROM table1
    INNER
      JOIN table2
        ON table2.col2 = table1.col2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    18
    OMGoodness It works like charm

    The synthax i wrote was different which I guess explains why it didnt work out ..


    Okay now I have another question ..
    What's the difference between INNER JOIN and NATURAL JOIN ?

    Thanks a lot for your help ^^/

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by madkitty View Post
    What's the difference between INNER JOIN and NATURAL JOIN ?
    the difference is in how you specify which columns to join on

    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
  •