Single AND operator and multiple OR operators

selectsqlite

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:

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.

Best Answer

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

Related Question