CODEWITHSUNDEEP
mysqlsql
Priya Jaiswal
Priya Jaiswal

Reputation: 75

SQL Select Statement with where, AND, OR not working correctly

Hello everyone i have the following row as a sample in my table 

id      shop_id     start_time      end_time
1       21          10:00           11:00

and i want to check whether start_time and end_time exist in table or not I am using following query but not working correctly, Where i am wrong ?

select * 
  from usr_booking 
 where shop_id='21' 
   and start_time between '10:10:00' and '11:01:00' 
    or end_time   between '10:10:00' and '11:01:00'

Upvotes: 0

Views: 1496

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

If you are given the times '10:10:00' and '11:01:00' and you want to know if anything overlaps with that period, then the logic is:

select b.* 
from usr_booking 
where shop_id = 21 and -- do not use single quotes for numeric constants (assuming `shop_id` is a number
      end_time > '10:10:00' and
      start_time < '11:01:00';

For overlapping intervals, between is not appropriate.

Upvotes: 0

Ram
Ram

Reputation: 84

Try this

SELECT * FROM usr_booking WHERE shop_id=21 AND (start_time BETWEEN '10:10:00' AND '11:01:00') OR (end_time BETWEEN '10:10:00' AND '11:01:00')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

You need to clearly separate the checks on the shop_id and the time ranges:

SELECT *
FROM usr_booking
WHERE
    shop_id = 21 AND
    (start_time BETWEEN '10:10:00' AND '11:01:00' OR
     end_time BETWEEN '10:10:00' AND '11:01:00');

The AND operator in MySQL has higher precedence than the OR operator. So, your current query is actually evaluating as this:

SELECT *
FROM usr_booking
WHERE
    (shop_id = 21 AND
     start_time BETWEEN '10:10:00' AND '11:01:00') OR
     end_time BETWEEN '10:10:00' AND '11:01:00';

Clearly, this is not the same logic as you what you probably intended.

Upvotes: 2

Nimesh Patel
Nimesh Patel

Reputation: 804

Try this Query:

SELECT *
FROM usr_booking 
Where shop_id='21' AND start_time BETWEEN '10:10:00' AND '11:01:00' 
AND end_time BETWEEN '10:10:00' AND '11:01:00'

Upvotes: 0

Sanjit Bhardwaj
Sanjit Bhardwaj

Reputation: 893

Try to make groups in such type of queries

select * from usr_booking where shop_id='21' AND ((start_time between '10:10:00' and '11:01:00') OR (end_time between '10:10:00' and '11:01:00'))

Add the extra brackets make groups.

Upvotes: 0

Related Questions