3

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.

0

1 Answer 1

6

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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