CODEWITHSUNDEEP
sqlmysqldatabase
aintghost
aintghost

Reputation: 11

combining a WHERE and IN clause with two fields

I have a table 'goods' with 3 columns (id, name, price). I need to get data by list of ids and price range.

I'm using this query:

SELECT id, name, price FROM goods
WHERE (id, price)
IN ((1,10), (3,20), (2,10))
ORDER BY FIELD(id, 1,3,2)

Everything is just fine while I'm using strict price values.

In fact, I need to do query like:

SELECT id, name, price FROM goods
WHERE (id, price)
IN ((1, BETWEEN 0 AND 100), (2, BETWEEN 50 AND 150), (n, BETWEEN m AND k))
ORDER BY FIELD(id, 1,3,n)

I found expression (1, BETWEEN 0 AND 100) will never return the result I need, while (1,10) will.

I'm surely can exclude records by price range in php, but I'm trying to find more efficient way to do it in mysql query using indexes. What should I do?

Upvotes: 1

Views: 1231

Answers (1)

Mark Byers
Mark Byers

Reputation: 838376

You can't use BETWEEN like that. Try changing your IN expression to a series of ORs instead:

SELECT id, name, price
FROM goods
WHERE
    (id = 1 AND price BETWEEN 0 AND 100) OR
    (id = 2 AND price BETWEEN 50 AND 150) OR
    (id = n AND price BETWEEN m AND k)
ORDER BY FIELD(id, 1,3, n)

Upvotes: 4

Related Questions