Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: Newbie MySql question about comparing columns in 2 tables

    This forum has been really helpful in gaining knowledge and trying to get up to speed on MySql. I am brand new to Mysql but learning as I go.

    I have 2 tables with 2 columns (Product and Inventory) and I am trying to compare the tables and create a new table that has the updated Inventory column and trying to find the right syntax. Table 1 is a Master list of 15k records and table 2 has 2k records.


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

    I think I need to use an INNER JOIN and I know this is a simple stupid question but to a newbie I am kinda lost and hoping someone can point me in the right direction. Any input is greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you tried to run that query?
    if so what was the result?
    and did that result meet your requirements?

    the where table1.thiscolumn = table2.thatcolumn although still valid is the old style syntax
    these days its recommended that you use the join syntax
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2013
    Posts
    4
    I am definitely doing something wrong here when I use the syntax

    SELECT *
    FROM table1, table2
    WHERE table1.QTY = table2.QTY
    ORDER BY table.SKU

    I get 306,869 records that combine the 2 tables with with 4 rows.

    Table 1 is the master list has 2 tables SKU and QTY with 15k records and Table 2 is a client table that has some of the SKU colums but has dummy QTY (1, 2,3) what I am trying to query is comparing table2 against table one and getting a list of the updated QTY. I know I am doing stuff wrong here.

    PS that Tiger 800 is awesome. I am just a fella that toodles around on a Honda cb350 trying to slowly turn into a cafe racer..

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    joining tables on quantity seems odd to me.
    i would expect you to be joining on the sku or product number

    or under certain circumstances the SKU AND quantity
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2013
    Posts
    4
    Your right joining tables on quantity is odd and will be joining the table with sku/product number. This site has been very informative and learned quite a bit from posters on here. Since I am total rookie I will keep poking around doing research on this topic.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by echoparker View Post
    This site has been very informative and learned quite a bit from posters on here. Since I am total rookie I will keep poking around doing research on this topic.
    good for you, thats a great attitude. This site can't hope to teach people, but it can help with specific issues and generally will try to point you in the right direction
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2013
    Posts
    4
    After more digging on DB forum a posting had a link to a solution of what I was trying to do. There are many ways to skin a cat but I wanted to get some input if it is good logic/correct usage or there is a better way to do the query.


    UPDATE table1, table2
    SET table2.QTY = table1.QTY
    WHERE table2.SKU = table1.SKU

    This seemed to work well so I just thought I would share.. Great info on this site and wicked smart and helpful peeps...

    "And I ride and I ride" (my cafe racer ha) - Iggy Pop

Posting Permissions

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