Home Pick only the first value in an IF statement inside a WHERE clause
Reply: 1

Pick only the first value in an IF statement inside a WHERE clause

tem_a_1
1#
tem_a_1 Published in 2018-02-14 18:46:09Z

I have a query like this:

... WHERE ... AND (IF(category.discount_value > 0, product_sizes.product_price * (1-category.discount_value/100),
        IF(sub_category.discount_value > 0,product_sizes.product_price * (1-sub_category.discount_value/100),
        IF(discount.product_id = product.id AND discount.from_date < now() AND discount.to_date >= now() AND discount.status = 1, product_sizes.product_price * (1-discount.percent/100),product_sizes.product_price)
        )
        )) >= 100 ...

Note that this is a part of the query, it is a long query so I'm trying to make it simpler to understand my problem.

And consider the table product_sizes:

product_id | price
         1 | 50
         1 | 110

Basically, I'm checking for a discount on the category, if there's no discount, I check if there's one for the subcategory, and finally if the item itself has a discount if both are false.

The problem is, I want the to take the first item (with price 50) when using product_sizes.price in the code here and use it as the maximum/minimum value so in this case it won't show up in the result, but it is now taking the last one instead most of the times.

I tried to add an alias, but then I can't use that alias in the WHERE clause, and the HAVING clause solution ignores GROUP BY so it is also not working.

Also, a subquery can't use a value from the parent query so it won't work for me. I'm using a product.id given by the query itself too.

I hope you can understand what's the issue. I'm looking for something like a function or something to use like this FIRST_V(product_sizes.product_price).

Thanks in advance.

Nsingh
2#
Nsingh Reply to 2018-02-14 21:59:10Z
select price from (select product_id,min(price) as price from product_sizes group by product_id)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.337074 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO