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_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
Thanks in advance.