-4
SELECT price from items Where categoryId = 11724 AND price BETWEEN 10 AND 20

Result:
 price
17.99
18.95
20.0
17.99
18.95

Above query works fine but adding another clause doesn't show desired results

SELECT price from items
Where categoryId = 11724
    AND ( brand = 'Akaso' OR brand = 'Andoer')
    and price BETWEEN 10 AND 20

Result: 0 rows found

SELECT price from items
Where categoryId = 11724
    AND brand = 'Akaso' OR brand = 'Andoer'
    and price BETWEEN 10 AND 20

Result:
 price
17.99
78.95
77.0
80.99
102.95

Have tried with and (column and column) for each clause. price type is VARCHAR
CHANGED TO Decimal(10,2) Where am i doing wrong?

RanaHaroon
  • 445
  • 6
  • 20
  • 2
    Possible to add a table with some data here? It will give a clear view to other developers. – Sahil Patel Jul 06 '17 at 17:30
  • 3
    maybe `SELECT price from items Where categoryId = 11724 AND ( brand = 'Akaso' OR brand = 'Andoer' ) and price BETWEEN 10 AND 20` doesn't return any values because there are none matching. – Alex Tartan Jul 06 '17 at 17:32
  • 2
    `price` being a `VARCHAR` is likely to cause you all sorts of issues, or at very least make things harder to read/predict; for example "100" is between "10" and "20", but MySQL _might_ cast `price` to an int when using `BETWEEN 10 AND 20`, which would make 100 fall outside the range; without encyclopedic knowledge of MySQL's auto casting, it will be hard to be sure. – Uueerdo Jul 06 '17 at 17:33
  • 1
    Well, having the price column as varchar is wrong, it should be numeric. However, this is not the primary issue here. The problem with your question is that we do not know your data, nor what you are trying to achieve here, so we cannot provide you with any solution. Pls follow this guidance on how to ask a good sql related question: https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Shadow Jul 06 '17 at 17:34
  • 1
    What are the results if you change the first query to `select price, brand from...`? If none contain 'Akaso' or 'Andoer', the second query should not be getting any results. – Uueerdo Jul 06 '17 at 17:35
  • The 2nd query having or with () should work. I'd guess you don't have any items which are 'Akaso' or Andoer' (at least w/o spaces or non-display characters...) or you need to cast price as a decimal. Rule of thumb. If you need to do math on it; store it as an numeric value. if you'll never do math on it store it as character. If it's a date. Store it as a date. Unless you're dealing with Extremely large or small numbers, use decimal or int; if large/small float may be what you need. but you'll lose precision – xQbert Jul 06 '17 at 17:36
  • Change `(brand = 'Akaso' OR brand = 'Andoer')` to `brand in ( 'Akaso', 'Andoer')`. Probably won't make a difference though. Cleaner code. Deal with the `price varchar` type first. – SS_DBA Jul 06 '17 at 17:38
  • @Uueerdo what column type should i set to avoid losing it's values as there's a lot of rows with 17.3 or 11.00 or 122.0 values? – RanaHaroon Jul 06 '17 at 17:47
  • [MySQL Numeric Data Types](https://dev.mysql.com/doc/refman/5.7/en/numeric-types.html) Depending on the kind of precision you need, likely the fixed-point or floating-point types. – Uueerdo Jul 06 '17 at 18:04
  • i have changed varchar type to decimal(10,2). prices are not varchar anymore now. it's like `12.89`, `13.39`, `43.94` but still it does not show values between 10 to 20 using between. – RanaHaroon Jul 07 '17 at 06:51

2 Answers2

1

I don't have your data, but I feel like everything is working properly and your last query is trying to do this:

SELECT price from items
Where (categoryId = 11724
AND brand = 'Akaso') OR (brand = 'Andoer'
and price BETWEEN 10 AND 20)
CptMisery
  • 612
  • 4
  • 15
  • You should try changing your select to `select categoryId, brand, price` to see which records it is returning – CptMisery Jul 06 '17 at 19:20
  • Thanks for answering. but still it's showing same results. even i changed varchar type to decimal(10,2). passing values are `10`,`20` column values are `11.23` , `23.00`. including brands makes trouble. without brands i am receiving desired results. as i said this query works fine. `SELECT price,categoryId,brand from items Where categoryId = 11724 and price BETWEEN 10 AND 20` – RanaHaroon Jul 07 '17 at 06:58
0

Thanks to all for replying. this worked for me after changing VARCHAR type to Decimal: 

SELECT price,categoryId,brand from items 
Where categoryId = 11724 
and (brand = 'Akaso' OR brand = 'Andoer') 
and (price BETWEEN 40 AND 50)

SELECT price,categoryId,brand from items 
Where (categoryId = 11724)
and (brand = 'Akaso' OR brand = 'Andoer') 
and (price BETWEEN 40 AND 50)

SELECT price,categoryId,brand from items 
Where categoryId = 11724 
and (brand = 'Akaso' OR brand = 'Andoer') 
and price BETWEEN 40 AND 50

Below Didn't work:

SELECT price,categoryId,brand from items 
Where categoryId = 11724 
and brand = 'Akaso' OR brand = 'Andoer' 
and (price BETWEEN 40 AND 50)

Curious to know difference between 3rd and 4th queries.

RanaHaroon
  • 445
  • 6
  • 20