Take the 2-minute tour ×

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
add comment

1 Answer

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 
    category_id=1 OR 
    category_id=2 OR 
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
AND operator (should) have a higher precedence than OR in all SQL dialects. –  ypercube May 15 '12 at 7:09
.. 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
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
show 2 more comments

Your Answer


