Filter results based on data from another table I have 3 three tables, products, discounts, and discounted_products:
products stores title, desc, price and etc information about products;
discounts stores discounts' start date, end date, and status;
discounted_products stores product_id (FK), discount_id (FK), discount type (percentage, static), discount amount, and status.

I need to filter and sort products by price ascending order, let's say greater than 100 and less than 150. But before querying the discount amount should be taken into consideration. Only some products have a discount, not all of them. How should I do it effectively?

I guess, first of all, active discounts should be extracted:

SELECT `dicount_id` WHERE CURDATE() &gt; `start_date` AND CURDATE() &lt; `end_date` AND `status` = 2


But after that I have no idea what to do. 

Edit: detailed explanation

Let me graph it quickly:

Products table:

id: 1   name: A  price: 200
id: 2   name: B  price: 110
id: 3   name: C  price: 130
id: 4   name: D  price: 180
id: 5   name: E  price: 145


Discounts table:

id: 1   name: DA start_date: 2018-09-15 end_date: 2018-09-30    status: 2
id: 2   name: DB start_date: 2018-09-20 end_date: 2018-10-01    status: 2
id: 3   name: DB start_date: 2018-09-10 end_date: 2018-09-20    status: 2


Discounted_products table:

id: 1   discount_id: 1  product_id: 1 type: 1 (percent) amount: 30 status: 2
id: 2   discount_id: 1  product_id: 2 type: 2 (fix) amount: 45 status: 2
id: 3   discount_id: 2  product_id: 4 type: 2 (fix) amount: 60 status: 2


I wanna query products in 100 - 150 price range. 
If there would be no active discounts it will return: from products table: 2, 3 and 5 (both in range 100 - 150)
Since we have two active discounts: from discounts table id : 1 and 2

new price table will look like:

id: 1   name: A  price: 140 =&gt; 200 - 200*30%
id: 2   name: B  price: 75  =&gt; 110 - 45     
id: 3   name: C  price: 130
id: 4   name: D  price: 120 =&gt; 180 - 60
id: 5   name: E  price: 145


New result should be: 4, 3, 1, 5
Start with that: 

select * from products where price &gt; 100 and price &lt; 150; 


Then... 

select * from products p
left join discounted_products dp on dp.product_id = p.id
left join discounts d on dp.discount_id = d.discount_id 
 where (price+IFNULL(d.amount,0)) &gt; 100 and (price+IFNULL(d.amount,0)) &lt; 150;


I do not have any idea about the column names you are using, so... 

Is that what you want?