How to join each order with its price?

Question

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

Answer 1

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