Need help with correct SQL

Question

So I'm a noobie, starter, beginner - throw all of the above at me. I'm trying to create a query that will search through a database and return blah blah blah. The problem is, it isn't quite working. Here's an example query - As you can see, above other things, I am trying to return results from someone with the last name Johnson

SELECT BookingInfo.ClinicID, BookingInfo.BookingDate, BookingInfo.BookingTime, 
  BookingInfo.Status, PatientBooking.FirstName, PatientBooking.LastName, 
  PatientBooking.DateOfBirth 
FROM BookingInfo LEFT JOIN PatientBooking 
  ON BookingInfo.PatientID = PatientBooking.PatientID 
WHERE PatientBooking.LastName = 'Johnson' AND BookingInfo.ClinicID = '1' 
  OR BookingInfo.ClinicID = '2' 
ORDER BY BookingInfo.BookingDate DESC 

This returns results with Johnson, but others as well. Another:

SELECT BookingInfo.ClinicID, BookingInfo.BookingDate, BookingInfo.BookingTime, 
  BookingInfo.Status, PatientBooking.FirstName, PatientBooking.LastName, 
  PatientBooking.DateOfBirth 
FROM BookingInfo LEFT JOIN PatientBooking 
  ON BookingInfo.PatientID = PatientBooking.PatientID 
WHERE BookingInfo.BookingDate = '05-18-2010' AND BookingInfo.ClinicID = '1' 
  OR BookingInfo.ClinicID = '2' 
ORDER BY BookingInfo.BookingDate DESC 

This returns results from the date I specified, but others as well. Am I doing something wrong with my syntax? Have I no clue what I'm doing? Please help a beginner out. Thanks!

Answer 1

Review the order of precedence between AND and OR.

In arithmetic, multiplication has higher precedence than addition.

Example: 10+10*10 = 110, but (10+10)*10 = 200.

It's similar with AND and OR. AND has higher precedence than OR, so this without parentheses:

WHERE BookingInfo.BookingDate = '05-18-2010' AND BookingInfo.ClinicID = '1' 
  OR BookingInfo.ClinicID = '2'

works like this:

WHERE (BookingInfo.BookingDate = '05-18-2010' AND BookingInfo.ClinicID = '1') 
  OR BookingInfo.ClinicID = '2'

But you want it to work like this:

WHERE BookingInfo.BookingDate = '05-18-2010' AND 
  (BookingInfo.ClinicID = '1' OR BookingInfo.ClinicID = '2')

So put in the parentheses to make sure the order of precedence works how you want it to.

---

I also just noticed you are using dates in MM-DD-YYYY format, which is not recognized by MySQL for date literals. You must use YYYY-MM-DD format. That could be causing a different problem.

SELECT DATE('05-18-2010'); -- returns NULL
SELECT DATE('2010-05-18'); -- returns 2010-05-18

---

Re your comment:

Are you sure that AND has a higher precedence?

Yes, I'm sure AND has higher precedence than OR. For one thing, the hierarchy of precedence of all operators in MySQL is documented here: http://dev.mysql.com/doc/refman/5.1/en/operator-precedence.html

Let's go through an example using your originally stated problem:

BookingDate   ClinicID 
2010-05-18    2
2008-05-18    2

WHERE BookingInfo.BookingDate = '2010-05-18' AND 
  BookingInfo.ClinicID = '1' OR BookingInfo.ClinicID = '2'

Using this expression, only the first row should match. But you found that both rows match, even though the date of the second row is not right. Why? Let's replace each comparison with either TRUE or FALSE:

TRUE AND FALSE OR TRUE
FALSE AND FALSE OR TRUE

If OR had higher precedence, it would evaluate like this:

TRUE AND (FALSE OR TRUE)
FALSE AND (FALSE OR TRUE)

Since any value combined with OR TRUE yields TRUE, the subexpression inside these parentheses would reduce to:

TRUE AND (TRUE)
FALSE AND (TRUE)

And the second row would not match, because FALSE AND TRUE yields FALSE. But that can't be, since you found the second row incorrectly matches.

In fact, AND has higher precedence than OR, so it really evaluates as if you had parentheses around the AND subexpression:

(TRUE AND FALSE) OR TRUE
(FALSE AND FALSE) OR TRUE

Which reduces to:

(FALSE) OR TRUE
(FALSE) OR TRUE

In both cases, FALSE OR TRUE yields TRUE, and both rows match.

So without parentheses, the default semantics are that AND has higher precedence than OR. You need the parentheses:

WHERE BookingInfo.BookingDate = '2010-05-18' AND 
  (BookingInfo.ClinicID = '1' OR BookingInfo.ClinicID = '2')

Answer 2

It's probably because you don't have parenthesis around your OR condition. Try the updated version below:

SELECT BookingInfo.ClinicID, 
       BookingInfo.BookingDate, 
       BookingInfo.BookingTime,
       BookingInfo.Status, 
       PatientBooking.FirstName, 
       PatientBooking.LastName, 
       PatientBooking.DateOfBirth 
FROM       BookingInfo 
LEFT JOIN  PatientBooking 
ON         BookingInfo.PatientID = PatientBooking.PatientID 
WHERE      PatientBooking.LastName = 'Johnson' 
AND        BookingInfo.ClinicID IN ('1', '2') 
ORDER BY   BookingInfo.BookingDate DESC 

Also, adding a little formatting to your SQL will make it much more readable. This will help you get answers on SO as well as help anyone else who ends up looking at your code.