Help with AND OR syntax SQLite

Databases
1

Easy question. Let’s say I want to query the database table as such:
Cars bought in 2020
Engine size V8
Color red or black

Is the sql statement set up as:
SELECT * FROM table WHERE CarsBought = '2020' AND EngineSize = 'V8' AND Color = 'Red' OR Color = 'Black'
Assuming this shows all red cars with a V8 engine bought in 2020 and all black cars with any engine size and purchased anytime

Or as:
SELECT * FROM table WHERE CarsBought = '2020' AND EngineSize = 'V8' AND Color = 'Red' OR CarsBought = '2020' AND EngineSize = 'V8' AND Color = 'Black'
I assume this is the correct way

Do you have to do all the ANDs then the OR followed by all the ANDS again? This always confused me. Or can I say I want to see this AND this AND this OR this. Guess I’m looking for the easy way out without having such long lines of code :slight_smile:

2

Use parentheses:

SELECT * FROM table WHERE CarsBought = '2020' AND EngineSize = 'V8' AND (Color = 'Red' OR Color = 'Black')

Go to the SQLite website (www.sqlite.org) and look up 'operator precedence".

3

could also work
AND Color IN (‘Red’,‘Black’)

other way is to get this few data by a primary sql filter and filter the rest in the xojo language.

1 Like
4

Awesome! Thank you! That makes sense and is easy to understand and read

5

exclude should be
where xyz not in (1, 2, 3)