CODEWITHSUNDEEP
mysqlsql
TiernO
TiernO

Reputation: 447

SQL where value OR value is between range

So basically I have the following query

SELECT t.id
     , t.number
     , t.capacity
     , b.reservation_code
     , b.reservation_date
     , r.start_time
     , r.end_time 
  FROM tables t 
  JOIN bookings b 
  JOIN reservation_time_data r 
    ON r.id = b.reservation_time 
 WHERE b.table_no = t.number 
   AND reservation_date LIKE '%2020-07-25%' 
   AND r.start_time BETWEEN '19:00:00.0000' AND ADDTIME('19:00:00.0000', '1:45:0.000000')

This functions as intended, but I need to add to add an OR clause. Id like it to be where r.start_time OR r.end_time is between...

I have tried doing WHERE r.start_time OR r.end_time BETWEEN...

and I have tried adding OR r.end_time BETWEEN..

to the end of the query. Each of this doesn't work as it should return me just one extra row (the original returns 2). But when I run either of the 2 attempts it returns me like 400+ rows. There isn't even 400 rows in the targeted table haha

Upvotes: 0

Views: 65

Answers (1)

Aleks G
Aleks G

Reputation: 57306

On the assumption that the duration is always 1:45:00...

You have to specify BETWEEN separately for each of the two time fields:

AND (
  r.start_time BETWEEN '19:00:00.0000' AND ADDTIME('19:00:00.0000', '1:45:0.000000')
  OR
  r.end_time BETWEEN '19:00:00.0000' AND ADDTIME('19:00:00.0000', '1:45:0.000000')
)

Upvotes: 1

Related Questions