WHERE clause followed by OR then WHERE again

Question

I will post only the part of the sql statament that contains WHERE clause:

//Together
WHERE `energy_extra`.`PacketFk` IN('7')
OR `energy_extra`.`house_extra_id` IN('4')

//Not together (separate where statements)
AND `People_from` <= '4'
AND `People_to` >= '4'
AND `Size` >= '100'
AND `Size` <= '200'
AND `houseconstructiontypelist`.`ConstructionTypeFk` IN('1', '2')

I can't change the order of these lines because they are a part of a much larger query written in codeigniter active record with conditional statements. What I can change is the SQL itself.

I don't get the desired result because WHERE foo IN ('7') OR bar IN ('4') is followed by AND thus SQL thinks those further conditions are a part of the OR bar IN() which they are not. They need to start with a new WHERE statement and every line after that can start with AND again.

I tried this but it doesn't work:

//Together
WHERE `energy_extra`.`PacketFk` IN('7')
OR `energy_extra`.`house_extra_id` IN('4')

//Not together (separate where statements)
WHERE `People_from` <= '4'
AND `People_to` >= '4'
AND `Size` >= '100'
AND `Size` <= '200'
AND `houseconstructiontypelist`.`ConstructionTypeFk` IN('1', '2')

Logic is as follows:

find rows where energy_extra.PacketFk is equal to 7 or where energy_extra.house_extra_id is equal to 4

then check all the other conditions

I'm sorry, do you mean something like: `WHERE (something OR something) AND somethingelse`? — Aioros, Feb 25 '16 at 13:41

Andrey Korneyev · Accepted Answer · 2016-02-25T13:45:51.007

2

Logical OR operator has less priority than AND operator - thus you're getting this result.

You need just enclose your first condition in brackets:

WHERE 
(
`energy_extra`.`PacketFk` IN('7')
OR `energy_extra`.`house_extra_id` IN('4')
)
AND ....

edited Feb 25 '16 at 13:45

answered Feb 25 '16 at 13:41

Andrey Korneyev

26,353
15
70
71

And brush up on SQL Logic Operator Precedence: And and Or http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or – Adrian Lynch Feb 25 '16 at 13:44
This is it, so simple. Thank you! – Short Port Feb 25 '16 at 13:46

score 1 · Answer 2 · answered Feb 25 '16 at 13:44

You could also put the first condition (containing or) to last as below : -

WHERE `People_from` <= '4'
AND `People_to` >= '4'
AND `Size` >= '100'
AND `Size` <= '200'
AND `houseconstructiontypelist`.`ConstructionTypeFk` IN('1', '2')
And `energy_extra`.`PacketFk` IN('7') OR `energy_extra`.`house_extra_id` IN('4')

Or just enclose the or statement in bracket to separate it from other statements as commented in comment section.

score 0 · Answer 3 · answered Feb 25 '16 at 13:46

You can't do where where in the same query (codeigniter does it (->where() ... ->where()) but it transforms the other where in AND or OR if specified), I am not sure what do you need exactly but the solution is make a query by hand and palce some brackets to separate AND, OR. Codeigniter active record it is not very good in that.

WHERE clause followed by OR then WHERE again

3 Answers3