CODEWITHSUNDEEP
mysqlsqlrange
fefe
fefe

Reputation: 9053

select more ranges in mysql

How do I SELECT multiple ranges in MySQL taking in consideration the following scenario: let say I want to get back from my product table products where

price 0 - 300 and price 600-1000 price 1200-1600

I was trying the following 

SELECT * FROM products WHERE price > 0 
  AND price <= 300 
  AND price >= 600 
  AND price <= 1000

but doesn't return any rows

Upvotes: 1

Views: 98

Answers (4)

Koryu
Koryu

Reputation: 1381

SELECT * FROM products WHERE 
(price > 0 AND price <= 300 )
OR
(price > 600 AND price <= 1000)
OR
(price > 1200 AND price <= 1600 )

you can also use between 

price BETWEEN 0 AND 300
or
price between ...

consider that between price BETWEEN 0 AND 300 will include 0 it will be parsed into price >= 0 AND price <= 300

Upvotes: 1

Alexander Myshov
Alexander Myshov

Reputation: 3101

You certanly need something like this:

SELECT *
FROM products
WHERE
    (price > 0 AND price <= 300) OR
    (price > 600 AND price <= 1000) OR
    (price > 1200 AND price <= 1600)

Upvotes: 0

h2ooooooo
h2ooooooo

Reputation: 39542

If a number is between 0 and 300 it can't also be between 600 and 1000. Are you looking for OR? Perhaps with BETWEEN just to make things easier?

SELECT * FROM products WHERE
    price BETWEEN 0 AND 300
    OR
    price BETWEEN 600 AND 1000
    OR
    price BETWEEN 1200 AND 1600

Upvotes: 2

dpk
dpk

Reputation: 641

use 'or' condition instead of 'and'

Upvotes: 0

Related Questions