Complex Select Query with multiple conditions and orders

Question

SELECT p.*
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id

LEFT JOIN Offers o
       ON ot.offer_id = o.id
      AND o.dF<=3 
      AND o.dt>=3

ORDER BY o.id DESC, 
          p.prod_id ASC
LIMIT 20

Please check the output in fiddle

I want the existing condition as it is and some more added conditions as listed below:

In this output, I got Offered products are shown first (This is important, after this)
I want New products - that is sort based on todaysDate - p.pdate less than 30 days
Then show the products those have p.pre != ''
Then show them who dont have price ie. p.price ==0
Then arrange other products by prod_id DESC

I want all in one, to add those 5 points in this existing query

I hope you got the problem. I need an immediate response. Thanks in advance

The points you mentioned in 1 to 5 are just for sorting the final output you get using the query you posted? — nawfal, Oct 26 '12 at 08:19
I want all in one... to add those 5 points in this existing query — Anusha, Oct 26 '12 at 08:21

score 1 · Accepted Answer · answered Oct 26 '12 at 08:50

Give this a go:

SELECT p.*, o.id
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id
LEFT JOIN Offers o
       ON ot.offer_id = o.id
      AND o.dF<=3 
      AND o.dt>=3
ORDER BY o.id DESC, 
case when p.pDate > curdate()-interval 30 day then p.pDate else '1970-01-01' end desc,
case when p.pre != '' then 1 else 0 end desc,
p.price asc,p.prod_id DESC
LIMIT 20;

I am a little unclear what you mean in point 2:

Do you want the resulting dates sorted DESC or ASC? What happens to those rows with a date older than 30 days? Exclude? Drop into the next sorting level?

I've taken a guess at what you meant. I am sure you can play around with the query if I have misunderstood.

this is exactly what I wanted.. I just modified little things.. you can see that here http://sqlfiddle.com/#!2/3da57/9 and thanks u so much @Tom Mac — Anusha, Oct 26 '12 at 09:26
2nd point was.. if the date interval is not in 30 days then that product should be sorted by `prod_id` like other products. this is also working there — Anusha, Oct 26 '12 at 10:04

score 0 · Answer 2 · answered Oct 26 '12 at 08:30

you simply have to add a WHERE condition...

SELECT p.*, o.id
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id

LEFT JOIN Offers o
       ON ot.offer_id = o.id
      AND o.dF<=3 
      AND o.dt>=3

WHERE datediff(curdate(),p.pdate)<30
      AND p.pre <> ''
      AND p.price = 0

ORDER BY o.id DESC, 
         p.prod_id ASC,
         prod_id DESC

LIMIT 20

Complex Select Query with multiple conditions and orders

2 Answers2