mysql,select query ,and or clause problem

Question

i have a table named item with four attribute name,code,class,value

now i want to group them in following way:

group a: name='A',code=11,class='high',value between( (5300 and 5310),(7100 and 7200),(8210 and 8290))
group b: name='b',code=11,class='high',value between( (1300 and 1310),(2100 and 2200),(3210 and 3290))

how can i do it?

Answer 1

First group

 select * from item
 where name LIKE 'A'
 and code LIKE '11'
 and class LIKE 'high'
 and (value BETWEEN 5300 AND 5310 OR value BETWEEN 7100 AND 7200 OR value BETWEEN 8210 AND 8290)

the same idea for group b

Answer 2

You might want to try something like this:

SELECT
    CASE
        WHEN code = 11 AND
             class = 'high' AND
            (code BETWEEN 5300 AND 5310 OR
             code BETWEEN 7100 AND 7200 OR
             code BETWEEN 8210 AND 8290)
        THEN 'A'
        WHEN code = 11 AND
             class = 'high' AND
            (code BETWEEN 1300 AND 1310 OR
             code BETWEEN 2100 AND 2200 OR
             code BETWEEN 3210 AND 3290)
        THEN 'B'
        ELSE Unknown
    END AS name,
    *
FROM your_table
ORDER BY name

You might wish to change ORDER BY to GROUP BY and you should be aware that BETWEEN includes both endpoints.