query with two different tables inner join where

Discussion in 'MySQL' started by xbat, Feb 17, 2014.

0
  1. #1
    I am using inner join. But I think I am doing it wrong.. Want multiple tables to match but I am not sure how to use my where. Any pointers or suggestions would greatly be appreciated.
    example

    UPDATE table1 t1
    INNER JOIN table2 t2
    ON t1.id =t2.num

    WHERE
    INNER JOIN table3 t3
    ON SUBSTRING_INDEX(t1.id,'-',1) = t3.buy_id
    WHERE
    INNER JOIN table3 ON t2.name = table3.MOD
    SET t1.id=t2.id
     
    Solved! View solution.
    xbat, Feb 17, 2014 IP
  2. #2
    Updates are for sending data back to the database. I don't know if you can do it with multiple tables but I wouldn't.

    Are you trying to get data from the database?
    I'd be really worried about any table structure that stored id numbers in columns called "num", "name" or "mod"
     
    sarahk, Feb 17, 2014 IP
    ryan_uk likes this.
  3. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #3
    why would you be worried about tables called called "num", "name" or "mod" ? Just wondering? If you saw some of the stuff was done. man on man..
     
    xbat, Feb 18, 2014 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #4
    Id columns are normally called that and if they refer to a foreign table the column name should refer to the table and include id.

    I'm sure there's some really bad design out there, doesn't mean it should be though.
     
    sarahk, Feb 18, 2014 IP
    ryan_uk likes this.
  5. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #5

    Thank you. Yes its been something of a challenge but at least im making some progress.. So what I did was added a few comlumns to the table. However I am not querying it right. How would I query something like this?

    update

    table1

    column1=columna where column2=column3

    thanks
     
    xbat, Feb 18, 2014 IP
  6. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #6
    i figured this last part out.

    update table1 h
    JOIN table1 x on x.column1=h.column2
    SET h.column=x.column5
     
    xbat, Feb 20, 2014 IP