MySQL using and or together

[sonicthehedgehog]

Is it possible to have a MySQL query that uses both and and or? So it would be something like

SELECT * FROM table WHERE show=1 AND deleted=0 AND id=1 OR id=2

So show and delete are always shown and the when it comes to the id's as long as one of the numbers is present it's shown?

[niche]

Sure. Also, you'll need to use parens around the ORs

WHERE (show=1 AND deleted=0 AND id=1) OR (id=2)

Edited September 14, 2015 by niche

[sonicthehedgehog]

So if I've got say six different id's do they all need to be in brackets?

so OR (id=1) OR (id=2) OR (id=3) etc

Also I don't understand what you mean about parents

[niche]

Yes.

 

Parens short for parenthesis.

[justsomeguy]

Just pay attention to what you're telling it to do. This:

WHERE (show=1 AND deleted=0 AND id=1) OR (id=2)

Will match either of these 2 conditions:

show = 1, deleted = 0, id = 1

or

id = 2

If that's not what you're trying to do, then rearrange the parentheses to group the conditions the way you want them.

[sonicthehedgehog]

I want it to always have WHERE (show=1 AND deleted=0) but have the 'OR's to so I want the results to be:

 

show = 1, deleted = 0, id = 1

show = 1, deleted = 0, id = 1 or show = 1, deleted = 0, id = 2

show = 1, deleted = 0, id = 2

etc

[Ingolme]

It sounds like show = 1 and deleted = 0 are always a requirement, so you would use an AND operator for them while the id is capable of changing.

 

If I understood your requirements, this is the logic you're looking for:

show = 1 AND deleted = 0 AND ( id = 1 OR id = 2 )

[sonicthehedgehog]

You're right I only want some values to be set sometimes but others always. The trouble is there are around 75 different values that can be set as well as different combinations of those. So am I right in thinking that if I want some values to be set all of the time I keep them out of the brackets but if they may be part of the query then I add them to the bracket? Like this:

show = 1 AND deleted = 0 AND ( id = 1 OR id = 2 OR id = 3 ) AND name != ''

[justsomeguy]

Just write it so that it makes sense, pay attention to what you're telling the computer to do. If you have multiple conditions where any one of them could match and that's fine, then put all of them in parentheses with OR. The parentheses are there to group conditions, to make it explicit to the computer what you're asking for. If you know what you want it to do then you should be able to figure out how to group the different parts to achieve that.

[Ingolme]

If it's a known range, then you can just use the BETWEEN operator

WHERE id BETWEEN 1 AND 75

If it's a known list of values, then you can use the IN operator

WHERE id IN (1, 2, 3, 4, 8, 12, 75)