#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0

    Joining tables in MySQL


    I have 2 tables with company names (around 1,000 companies) and products (20,000 products).

    I am looking to create a table which would have all products against each companies. eg. "company#1" in column A each in rows 1 to 20k... and "products#1 to 20,000" in column B
    similarly for company#2" and so on

    - so I would have 1,000*20,000 = 20,000,000 records in table.

    Thanks
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    Joining tables in MySQL


    You can use Union to get the type of result you wanted.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0

    Thumbs up Error Code: 2008 MySQL client ran out of memory


    I have created DB (myDatabase) with 3 tables (Table1, Table2, Table3) each tables have 1 column - namely company, product1 and and product2 respectively.

    table1 has 5 records
    table2 has 6000 records
    table3 has 2000 records

    I am now using below query to create another table and join the results from all 3 tables - which would fetch me 60,000,000 rows (ie 5 * 6000 * 2000). (I know thats huge but I want all records for some analytics)

    However when I am running the query I am getting "Error Code: 2008 MySQL client ran out of memory". I am using MySQL workbench on my PC (Win 7-64 Bit, 8GB RAM and 600 GB free HD space) to run the code.

    Also if I change the limit (in below code) to lower number (say 1000) it executes the code and shows me results on screen but it does not insert the results into "myNewtable"

    Can someone help please.

    Create Table myDatabase.myNewtable ( SrNo INT NOT NULL AUTO_INCREMENT PRIMARY KEY, company VARCHAR(255), product1 VARCHAR(255), product2 VARCHAR(55) ); Select table1.company, table2.product1, table3.product2 from myDatabase.table1, myDatabase.table2, myDatabase.table3 LIMIT 0,99999999
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,127
    Rep Power
    4274
    Originally Posted by backbencher
    Also if I change the limit (in below code) to lower number (say 1000) it executes the code and shows me results on screen but it does not insert the results into "myNewtable"
    your SELECT is a separate statement from your CREATE TABLE

    you can define the CREATE TABLE including the SELECT as one statement (by removing the semicolon between them), but i would not do that, because after you've processed a few rows with your LIMIT, you're still going to have to use an INSERT SELECT statement for the remainder

    so CREATE the table, and then just start using INSERT SELECT statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    your SELECT is a separate statement from your CREATE TABLE

    you can define the CREATE TABLE including the SELECT as one statement (by removing the semicolon between them), but i would not do that, because after you've processed a few rows with your LIMIT, you're still going to have to use an INSERT SELECT statement for the remainder

    so CREATE the table, and then just start using INSERT SELECT statements
    Thanks a lot mate... that helped!
    ...after 1050 secs I got my data..

IMN logo majestic logo threadwatch logo seochat tools logo