1

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

  • 2
    Do you have tried something already? – Kondybas Sep 21 '18 at 18:44
  • Simply extend your question with your last try, it is needed by the answerers to understand, what exactly you want to know. – peterh Sep 21 '18 at 18:47
0

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 
0

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?

  • First of all, thank you for your help. I just added a detailed explanation to my question, could you check it out, please? – Shahin Sep 21 '18 at 20:21
  • Right i will rewrite it. but not today i am a little busy. But now is more clear. You will need a subquery in the second left join with case when to check the status and do the right thing with the amount,.. – Luciano Andress Martini Sep 21 '18 at 20:42
  • I really appreciate your help, @LucianoAndressMartini . I tried to improve it. But what I am supposed to with discount types like percentage and fixed discounts? I guess, should be done with cases. SELECT * FROM products p LEFT JOIN discounted_products dp ON dp.product_id = p.id LEFT JOIN( SELECT id FROM discounts WHERE CURRENT_DATE > start_date AND CURRENT_DATE < end_date AND discounts.status = 2 ) AS d ON dp.discount_id = d.id WHERE (price + IFNULL(dp.amount, 0)) > 100 AND(price + IFNULL(dp.amount, 0)) < 150 – Shahin Sep 22 '18 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.