Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

MySQL WHERE help

12 views
Skip to first unread message

John Williams

unread,
Jul 11, 2002, 2:24:58 AM7/11/02
to
Hi All,

Almost finished with a project - but I am stumped (and no worldly amount of
caffeine could keep me up longer.)

Have a situation where I need to query the database on a single table
(Snackers) but looking at three fields.
Field1=CustomerName (Johnny Junkie)
Field2=LikesMMs (Yes/NULL)
Field3=LikesSnickers (Yes/NULL)

Always need to match CustomerName to "Johnny Junkie" value, but want to get
either or both of Field2/Field3 if they contain "Yes".

Here is my flawed query:
SELECT Dentist, PhoneNumber FROM Snackers WHERE CustomerName='Johnny Junkie'
AND LikesMMs='Yes' OR LikesSnickers='Yes'

This gives me Johnny's Dentist only if Johnny likes M&Ms, and to my dismay -
*any* other snack-lover that likes Snickers - DOLT!!!

The right syntax would give me *only* Johnny's preferences for M&M's,
Snickers, or both.

Any clues - Thanx a bunch! Trying to avoid two separate queries...

John


Chuck Joslin - <<TNO/TGF>>

unread,
Jul 11, 2002, 3:02:45 AM7/11/02
to
John -
 
When you have an AND and an OR, you need to group them appropriately to establish precedence.
 
Your original query was ambiguous. Try this:
 
SELECT Dentist, PhoneNumber FROM Snackers WHERE CustomerName = 'Johnny Junkie' AND (LikesMMs = 'Yes' OR LikesSnickers = 'Yes')

--
Chuck Joslin [Team NetObjects]
www.gotfusion.com
=============================
Awave, Inc., Orlando, Florida USA
=============================
Fusion 7/PHP/MySQL Tutorial
http://www.awave.net/html/fusiontutorials.php

Chuck Joslin - <<TNO/TGF>>

unread,
Jul 11, 2002, 3:21:30 AM7/11/02
to
John -
 
In further explanation....
 
AND takes precedence over OR in a query, so your original one was acting like this:
 
SELECT Dentist, PhoneNumber FROM Snackers WHERE (CustomerName = 'Johnny Junkie' AND LikesMMs = 'Yes') OR LikesSnickers = 'Yes'
which explains the results you were getting.
 
The WHERE clause options were simply being processed in order. The first two gave you only records where J.J. liked M&Ms. The last stood alone - giving you every record in the database where the person liked Snickers.
 
Interesting example. Got a sweet tooth? (-:
 
--
Chuck Joslin [Team NetObjects]
www.gotfusion.com
=============================
Awave, Inc., Orlando, Florida USA
=============================
Fusion 7/PHP/MySQL Tutorial
http://www.awave.net/html/fusiontutorials.php
"Chuck Joslin - <<TNO/TGF>>" <ch...@gotfusion.com> wrote in message news:agja69$ha...@news01.netobjects.com...

John Williams

unread,
Jul 11, 2002, 8:32:30 AM7/11/02
to
Thank you for the precedence example...that is exactly what was going on...
 
LOL - re:sweet tooth, I found myself digging through the pantry last night after posting the question - turned up nothing good like M&M's OR Snickers! :) Just some old chews from Halloween circa 1999. Yeah, I ate one...
 
Thanks Chuck for the quick reply and congrats on TNO/TGF... John
"Chuck Joslin - <<TNO/TGF>>" <ch...@gotfusion.com> wrote in message news:agjb9f$ha...@news01.netobjects.com...

Travis Elkins

unread,
Jul 11, 2002, 12:43:36 PM7/11/02
to
Chuck,
 
I was going to tell him that without the parentheses, the logic would be read from left to right, effectively resulting in the same thing that you described below: (x AND y) OR z.
 
But, you said that AND takes precedent over OR.  Is this always the case?  I thought they were equal.  Does that mean that if I wrote, "x OR y AND z" that it would be read as if it said, "x OR (y AND z)"?
 
Whatever the case, I ALWAYS use parentheses to make sure that the logic is carried out as I intended, but I was just curious.

--
Travis Elkins
Chidera Software
www.chidera.com
"Chuck Joslin - <<TNO/TGF>>" <ch...@gotfusion.com> wrote in message news:agjb9f$ha...@news01.netobjects.com...

Travis Elkins

unread,
Jul 11, 2002, 1:36:42 PM7/11/02
to
Chuck,
 
As always, thanks for the information and the insight.
 
Oh, and get to bed on time for a change!  ;-)

--
Travis Elkins
Chidera Software
www.chidera.com
"Chuck <<TNO/TGF>>" <ch...@gotfusion.com> wrote in message news:agkedu$hf...@news01.netobjects.com...
Travis -
 
SQL is a funny thing. The answer depends on the implementation.
 
I've experimented a bit with this a long time ago and found that x OR y AND Z does indeed produce x OR (y AND z) in some SQL dialects. I started using SQL in Access, so 'nuff said about that!
 
Removing ambiguity thru use of parentheses makes it a moot issue. I haven't experimented with it in MySQL recently. I learned long ago to just enclose operators appropriately to get the results I'm looking for.
 
To the technically astute, saying "AND takes precedence over OR in a query constructed like yours because it comes first, so your original one was acting like this:" would have been more accurate. Hey...it was 3AM when I wrote that. Gimme a break! (-:

--
Chuck Joslin «Team NetObjects»
www.gotfusion.com
Awave, Inc., Orlando, Florida USA
=============================
Fusion 7/PHP/MySQL Tutorial
http://www.awave.net/html/fusiontutorials.php

Chuck <<TNO/TGF>>

unread,
Jul 11, 2002, 1:22:58 PM7/11/02
to
Travis -
 
SQL is a funny thing. The answer depends on the implementation.
 
I've experimented a bit with this a long time ago and found that x OR y AND Z does indeed produce x OR (y AND z) in some SQL dialects. I started using SQL in Access, so 'nuff said about that!
 
Removing ambiguity thru use of parentheses makes it a moot issue. I haven't experimented with it in MySQL recently. I learned long ago to just enclose operators appropriately to get the results I'm looking for.
 
To the technically astute, saying "AND takes precedence over OR in a query constructed like yours because it comes first, so your original one was acting like this:" would have been more accurate. Hey...it was 3AM when I wrote that. Gimme a break! (-:

--
Chuck Joslin «Team NetObjects»
www.gotfusion.com
Awave, Inc., Orlando, Florida USA
=============================
Fusion 7/PHP/MySQL Tutorial
http://www.awave.net/html/fusiontutorials.php

Tami - TNO

unread,
Jul 11, 2002, 1:52:36 PM7/11/02
to
Oh John,
Too bad you don't live in my neighborhood. My hubby made it a condition of marriage that I keep the fridgy stocked with goodies, and candy bars. We have one whole shelf in ours dedicated to chocolate candy... Snickers, Twix, Doves, Mounds... you name it... Hubby gets upset when my galpals drop over and raid "HIS" stash.... LOL
 
Tami
--
TNO/TGF
www.gotfusion.com
0 new messages