Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wanted to select a record_id with a user_id of 1 and category_id can be 1, 2, or 3. Every SELECT statement should return 1 record.

View my sample table here: http://i49.tinypic.com/2ezjtk8.png

How do I do it in SQL? I tried this SQL statement:

SELECT record_id
FROM tbl_people
WHERE category_id=1 OR category=id=2 OR category_id=3
OR category_id=4 OR category_id=5 OR category_id=6 
AND user_id = 1;

...But it does not work.

share|improve this question
    
you can use the "between" too, where category_id between 1 and 6 and user_id=1; –  jcho360 May 15 '12 at 12:28

1 Answer 1

up vote 6 down vote accepted

That's because in SQLite, the AND operator has a higher precedence than OR (see the Operators section on this SQLite documentation page)

This means that SQLite first evaluates the category_id=6 AND user_id = 1 expression and then ORs its result with all the other category_id conditions.

Thus, your query returns all records where category_id is 1,2,3,4, or 5, or where category_id is 6 and user_id is 1.

You should add parentheses to force the ORs to be evaluated first:

SELECT record_id 
FROM tbl_people 
WHERE 
(
    category_id=1 OR 
    category_id=2 OR 
    category_id=3
)
AND user_id = 1;

Alternatively, you could use the SQL IN operator to make this more concise:

SELECT record_id 
FROM tbl_people 
WHERE category_id IN (1, 2, 3) 
AND user_id = 1;

Also, note that you have a typo: in your WHERE clause there's a category=id, which should probably be category_id.

Update: Here's a link to try out the query: http://www.sqlfiddle.com/#!7/d7ad3/1

share|improve this answer
3  
AND operator (should) have a higher precedence than OR in all SQL dialects. –  ypercube May 15 '12 at 7:09
4  
.. and in any language I've ever worked with. Why do folk stop thinking when it comes to SQL? –  gbn May 15 '12 at 7:11
    
Great answer! How do i get only one record_id per SELECT statement? –  Jayson Tamayo May 15 '12 at 8:05
    
@JaysonTamayo What is the rule by which you'd like to get one record_id? Would you like to take the min, max, or just the first that happens to be returned? –  w0lf May 15 '12 at 8:17
1  
you can return just the first result by adding a LIMIT 1 clause at the end of your query. Example: sqlfiddle.com/#!7/d7ad3/3 –  w0lf May 15 '12 at 8:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.