Filter results based on data from another table

Question

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() > `start_date` AND CURDATE() < `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 => 200 - 200*30%
id: 2   name: B  price: 75  => 110 - 45     
id: 3   name: C  price: 130
id: 4   name: D  price: 120 => 180 - 60
id: 5   name: E  price: 145

New result should be: 4, 3, 1, 5

Answer 1

You should try this one:

SELECT * 
FROM
  ( SELECT tb1.*,
           tb3.id AS hasDiscount,
           tb2.type,
           tb2.amount, 
           CASE WHEN tb3.id IS NULL THEN tb1.price
                WHEN tb2.type = 1 THEN tb1.price -(tb1.price * tb2.amount) / 100
                ELSE tb1.price - tb2.amount
           END AS FinalPrice
    FROM products tb1 
         LEFT JOIN discounted_products tb2
         ON  tb1.id = tb2.product_id AND tb2.status = 2
         LEFT JOIN discounts tb3
         ON  tb2.discount_id = tb3.id
         AND CURRENT_DATE() > start_date
         AND CURRENT_DATE() < end_date
         AND tb3.status = 2
  ) AS table1 
WHERE STATUS = 2 
  AND FinalPrice BETWEEN 0 AND 300 

Answer 2

Start with that:

select * from products where price > 100 and price < 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)) > 100 and (price+IFNULL(d.amount,0)) < 150;

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

Is that what you want?