CODEWITHSUNDEEP
mysql
Anibal Mauricio
Anibal Mauricio

Reputation: 553

Get records from the same table mysql

Thanks in advance.

I have to validate that a user is available at a certain time. I'm trying to make a double query to the same table by comparing the original record (the one that has the values ​​that we are going to compare with the date and time of the active record) with the rest of the table, so a user who wants to assign a schedule Must be validated that it is not assigned in another service at the same time or within a range of time (say 45 minutes before and after).

I have not been able to solve the problem and I would like to know if you could help me with this. This is the query I have so far but it does not return any results:

SELECT b2.*, b.* FROM bookingdetails b, bookingdetails b2
WHERE b.pickup_date = b2.pickup_date
AND b.pickup_time = b2.pickup_time
AND b2.driver_id = 109
AND b.id = 2449
AND b2.id <> 2449
AND b2.pickup_time BETWEEN b.pickup_time - INTERVAL 45 MINUTE AND b.pickup_time + INTERVAL 45 MINUTE

Upvotes: 0

Views: 19

Answers (1)

JNevill
JNevill

Reputation: 50019

I think you are on the right track, but you have two competing conditions:

b.pickup_time = b2.pickup_time

AND

b2.pickup_time BETWEEN b.pickup_time - INTERVAL 45 MINUTE AND b.pickup_time + INTERVAL 45 MINUTE

Get rid of that first one and I think it might work for you.

Also, it's worth noting that you are using an out of date JOIN syntax. Instead:

SELECT b2.*,
    b.*
FROM bookingdetails b
    INNER JOIN bookingdetails b2 ON 
        b.pickup_date = b2.pickup_date      
        AND b2.pickup_time BETWEEN b.pickup_time - INTERVAL 45 MINUTE
            AND b.pickup_time + INTERVAL 45 MINUTE
WHERE b2.driver_id = 109
    AND b.id = 2449
    AND b2.id <> 2449

Upvotes: 1

Related Questions