[RESOLVED] Is this allowed in MySQL?

[partypete]

Hello,

Is this allowed in a MySQL query?

SELECT * FROM table WHERE (field1 != '' OR field2 != '') AND field5 = 'xxxx' OR (field1 != '' OR field2 != '') AND field5 = 'yyyyy'

I'm talking about this part: (field1 != '' OR field2 != '')

That is allowed right? In MySQL 4.0.25?

Thanks.

[tomhath]

Seems to me it would've been easier to try it than post a question, but, yes it should work.

I'm not sure the rest of your WHERE clause will do what you expect however. I'd add some parenthesis to be sure the ANDs and ORs are combined they way you want.

[partypete]

My apologies. Lesson learned.

Thank you.

[Sxooter]

Definitely the thing with parenthesis is a good hint here. Without them you may be getting something other than what you expect. If you have a query like this:

... where A=1 AND B=2 OR C=3

does it mean:

... where A=1 AND (B=2 OR C=3)

or

... where (A=1 AND B=2) OR C=3

???

While there's certainly a priority in the query analyzer, it's possible you'll find two different databases giving two different answers based on how they implemented their parsers. Parenthesis are your friend... 🙂

[laserlight]

You may find it better to express it as:

SELECT * FROM table
	WHERE (field1 != '' OR field2 != '') AND (field5 = 'xxxx' OR field5 = 'yyyyy');

Better yet, especially if you want to test for even more possible values of field5:

SELECT * FROM table
	WHERE (field1 != '' OR field2 != '') AND field5 IN ('xxxx', 'yyyyy');

[Sxooter]

Yeah, in() is a very nice construct. It makes Oring a large list much simpler, and you can build it with implode:

$query = "select * from some table where field1='aaa' and field2 in(";
$query.= implode(",",$arrayofvalues);
$query.=")";

If they're text values, you'd need ' marks in there:

$query = "select * from some table where field1='aaa' and field2 in('";
$query.= implode("','",$arrayofvalues);
$query.="')";

[Sxooter]

Plus, you can invert an in with the fields in it:

select * from sometable where 'value' in (field1,field2,field3);