I have two tables. One table of Ids and their prices, and second table of discounts per Id. In the table of discounts an Id can has many Discounts, and I need to know the final price of an Id.
What is the Best way to query it (in one query) ? The query should be generic for many discounts per id (not only 2 as mentioned below in the example)
For example Table one
id price
1 2.00
2 2.00
3 2.00
Table two
id Discount
1 0.20
1 0.30
2 0.40
3 0.50
3 0.60
Final result:
id OrigPrice PriceAfterDiscount
1 2.00 1.12
2 2.00 1.20
3 2.00 0.40