Select multiple rows using id in SQLite

Question

I am trying to query specific rows from my SQLite database using WHERE conditions on the IDs of the rows I want. However, the query is not returning any results, and it is causing my logger to throw an error stating that there is a syntax error.

Here is the query:

Select * From Equipment Where ID = 2 and ID = 3 and ID = 4 and ID = 7 and ID = 11 and ID = 34

And here is the syntax error from my log:

Aug 17 2017 23:12:23  [Err]: Err002 - Query on Equipment could not be prepared: near "=": syntax error at File: c:\users\geowil\documents\visual studio 2015\projects\ov_pocs\universearc_poc\datasystem.cpp  Line: 323.

So I sailed over to SQLFiddle to try and see if I missed something. It is displaying the same behavior. With and conditions, it returns no results - but running queries on single IDs or a range of IDs works.

Am I doing something wrong or is this a limitation of SQLite?

Update:

Had a brain wave. Using Where ID IN(1,3,4) works on SQLFiddle so now I want to repurpose my question to ask why this works, but my original query did not.

Bismuthous

Answer 1

Use IN to select your multiple tuples.

Select * From Equipment Where ID IN (2, 3, 4, 7, 11, 34);

Every tuple will be selected if every ids existed in your SQLite or Database.

---

For your query, your logical equation will never select one of the tuple because an id won't have two or more values (ID=2 AND ID=3: if ID equal to 2 it won't be equal to 3). In your case you must use OR instead of AND.

Select * From Equipment Where ID=2 OR ID=3 OR ID=4 OR ID=7 OR ID=11 OR ID=34;

So that every tuple will be selected if every ids existed in your SQLite or Database.

Domino

Oh jesus. Duh, don't know why that did not occur to me lol. Thanks. – Bismuthous

You are welcome! Please help to check it as a correct answer! – Domino

Answer 2

Use IN instead of = like, issue will be fixed:

SELECT * FROM Equipment WHERE ID IN (1 , 2);

Farahfarand

Answer 3

AND means that the conditions on both sides must be true. But for any single row, there can only be a single ID value.

To check for a match with any one of multiple values, replace AND with OR, or use IN.

Cerelly