Skip to content
Advertisement

How can I use WHERE with multiple different columns?

Having trouble trying to query AND and OR in an SQlite3 statement.

Is there a way where I can do something like

WHERE section = ? AND LEVEL =? AND subj1 =? AND subj2=? AND subj3 =?

From what I see in this particular line

SELECT * FROM students WHERE section = ? AND level =? AND subj1 = ? OR subj2 = ? OR subj3 =?

It won’t work because after ‘AND subj1 =?’ SQlite3 now chooses to select whatever subject that subj2 and subj3 has, completely disregarding the section and level column. How can I get the rows that have match the section, level and subjects that I want?

Here’s my code

query = ("""SELECT * FROM students WHERE section = ? AND level =? AND subj1 = ? OR subj2 = ? OR subj3 =?"""
c.execute(query, (variable1, variable2, variable3, variable4, variable5, ))
rows = c.fetchall()
print (rows)   

Here’s a sample table

Name        Section       Level        Subject 1       Subject 2      Subject 3

John    |      1      |     1      |      Math     |    Science    |    English
Mary    |      1      |     1      |      Math     |    Science    |    English
Dave    |      2      |     2      |      English  |    History    |    Math 
Joe     |      2      |     2      |      English  |    History    |    Math
        

Advertisement

Answer

OR has lower logical precedence than AND. To do what you want, you need to surround the ORed predicates with parentheses:

SELECT * 
FROM students 
WHERE section = ? AND level = ? AND (subj1 = ? OR subj2 = ? OR subj3 =?)

However, here, it is simpler to use IN:

SELECT * 
FROM students 
WHERE section = ? AND level = ? AND ? IN (subj1, subj2, subj3)
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement