All geek questions in one place

Need help with proper SQL

So, I am a noobie, a starter, a beginner - I will throw all of the above on me. I am trying to create a query that will search through the database and return blah blah blah. The problem is that it does not quite work. Here is an example request. As you can see, among other things, I am trying to return results from someone with the surname 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. Other:

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. Am I something wrong with my syntax? Don't I know what I'm doing? Please help the newbies. Thank!

+3
source share
5 answers

The loan should go to @Randolph Potter:

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
0
source

Review the order of priority between AND and OR.

, .

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

AND OR. , OR, :

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

:

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

, :

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

, , , , .

, MM-DD-YYYY, MySQL . YYYY-MM-DD. .

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

:

, ?

, , , OR. -, MySQL : http://dev.mysql.com/doc/refman/5.1/en/operator-precedence.html

, :

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

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

, . , , . ? TRUE, FALSE:

TRUE AND FALSE OR TRUE
FALSE AND FALSE OR TRUE

OR , :

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

OR TRUE TRUE, :

TRUE AND (TRUE)
FALSE AND (TRUE)

, FALSE AND TRUE FALSE. , , .

, , OR, , :

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

:

(FALSE) OR TRUE
(FALSE) OR TRUE

FALSE TRUE TRUE, .

, , , OR. :

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

, , . :

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

, SQL . SO, -, .

+1

WHERE, OR.

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

but since it ANDhas a higher priority than OR, what you are actually doing is

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

Thus, you will see each line in which ClinicIDis equal to 2; not just Johnson.

0
source

If you put brackets around two ClinicID conditions, it should work.

See 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 = '1' OR BookingInfo.ClinicID = '2') ORDER BY BookingInfo.BookingDate DESC 

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
0
source

Source: https://habr.com/ru/post/1752551/

More articles:


All Articles