CODEWITHSUNDEEP
sqlsql-serverrangemultiple-columns
user4582995
user4582995

Reputation: 123

multiple column price range sql query

Not able to solve this query:

Want to search product with category, subcategory , lower price range and upper price range

select * from product p join category c 
ON(p.category_id=c.category_id) where p.category=? and p.sub_category_id=?
and lower_price_limit between ? and ? or upper_price_limit between ? and ?

For example: A product range from 200 to 250 and pass 220 to 230 this doesn't work.

Thanks

Upvotes: 0

Views: 668

Answers (3)

Kaushik Maheta
Kaushik Maheta

Reputation: 1891

select * 
from product p 
join category c ON p.category_id=c.category_id 
where p.category=? and p.sub_category_id=? and 
      (lower_price_limit between ? and ? or upper_price_limit between ? and ?)

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You forgot to add parenthesis on your WHERE clause:

SELECT * 
FROM product p 
JOIN category c 
    ON p.category_id = c.category_id
WHERE
    p.category = ? 
    AND p.sub_category_id = ? 
    AND (
        lower_price_limit BETWEEN ? AND ? 
        OR upper_price_limit BETWEEN ? AND ?
    )

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try this query :- 

 select * from product p join category c 
    ON p.category_id=c.category_id where p.category=? and p.sub_category_id=?
    and (lower_price_limit between ? and ? or upper_price_limit between ? and ?)

Upvotes: 0

Related Questions