How can I use WHERE with multiple different columns?

Question

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

GMB · Accepted Answer · 2020-11-20 13:07:15Z

1

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)

answered Nov 20, 2020 at 13:07

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-11-20 13:07:06Z

0

Use parentheses:

WHERE section = ? AND level =? AND
      (subj1 = ? OR subj2 = ? OR subj3 = ?)

Assuming the last three parameters are the same, you can simplify this using IN:

WHERE section = ? AND level =? AND
      ? IN (subj1, subj2, subj3)

Usually IN has constants in the IN list and column names as the left operand. However, that is not required.

answered Nov 20, 2020 at 13:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How can I use WHERE with multiple different columns?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related