0

I have a table set up that needs to have a field that calculates the price of an order. To do so, I have a 3-letter code set up for each item, but the price of these items isn't included in that table, but another table. To calculate the price, I need to multiply the quantity of the item by the price of the item. So basically, how do I associate the 3-letter code with the price of the item in the other table. The 3 letter code field is also included in the table with the price of the item

Andy G
  • 19,232
  • 5
  • 47
  • 69
accesssss
  • 3
  • 1
  • 3

1 Answers1

0

What you need is a join:

   SELECT table1.quantity * table2.price AS result
     FROM table1
LEFT JOIN table2
       ON table1.code = table2.code

Assuming table1 and table2 have the following fields (you might want to adjust as necessary):

  • code: the 3-letter code
  • quantity: the quantity of an item
  • price: the price of the item

Also see here for more background information: SQL JOIN and different types of JOINs

Leviathan
  • 2,468
  • 1
  • 18
  • 24
  • That shows how to join two tables, I think you can add something about performing the multiplication `table1.x * table2.y as result`. – Andy G May 29 '16 at 08:49
  • Thanks, that works really well! I have a field set up that I want to make the end result into, how would I do that? – accesssss May 29 '16 at 14:04
  • You shouldn't, that would introduce data redundancy that you never want to have in a database. Just use the query from now on instead of the table - it *looks* as though you have added the additional field, but in reality it is just calculated on-the-fly. – Leviathan May 29 '16 at 14:32