mysqlsqldatabaseinner-joindatabase-management

How to join each order with its price?

I have two tables named PRICES and ORDERS.

PRICES

Product_ID price
1 50
2 100

ORDERS

CUSTOMER_ID Product_ID QUANTITY
123 1 3
456 2 5
789 2 2
327 1 7

I want to join these two tables to get below expected output:

EXPECTED OUTPUT

Product_ID CUSTOMER_ID QUANTITY PRICE TOTAL
1 123 3 50 150
2 456 5 100 500
2 789 2 100 200
1 327 7 50 350
Solution

  • You can use inner join to get price for each product in orders table:

    Select o.customer_id, p.product_id, o.quantity, p.price
from PRICES p inner join ORDERS o on p.product_id=o.product_id