CODEWITHSUNDEEP
mysqlsql
Kannan Rajendran
Kannan Rajendran

Reputation: 220

Fetch Table data based on multiple timing referred from other table

I have 2 tables in which one has schedules and other table has the availability times. Structure is as follows

tbl_schedules

id   Int
userid   Int
schedulefrom   Datetime
scheduletill   Datetime


tbl_useravailability

id
userid
availablefrom
availabletill

There can be multiple rows for the user in tbl_useravailability for the same day. That is the user can be available from 2014:10:17 05:00:00 to 2014:10:17 12:00:00, 2014:10:17 15:00:00 to 2014:10:17 18:00:00, 2014:10:17 19:00:00 to 2014:10:17 23:00:00

The challenge is to pick the schedules for other users from the schedule table on the given availability day something like where userid <> 1;

Upvotes: 0

Views: 36

Answers (2)

Kannan Rajendran
Kannan Rajendran

Reputation: 220

Thanks for the link and effort spend to resolve this. I have made some modifications in your query and got this worked. Thanks

SELECT
    S.id AS scheduleid,
    S.userid,
    S.schedulefrom,
    S.scheduletill,
    UA.id AS availabilityid,
    UA.availablefrom,
    UA.availabletill
FROM
    tbl_schedules AS S
JOIN tbl_useravailability AS UA
    ON S.schedulefrom between  UA.availablefrom and UA.availabletill

WHERE
    UA.userid=1 and s.userid <> 1

Upvotes: 0

Joe
Joe

Reputation: 856

How about something like the following (view sample at http://sqlfiddle.com/#!2/f94e6/1/0):

SELECT
    S.id AS scheduleid,
    S.userid,
    S.schedulefrom,
    S.scheduletill,
    UA.id AS availabilityid,
    UA.availablefrom,
    UA.availabletill
FROM
    tbl_schedules AS S
JOIN tbl_useravailability AS UA
    ON  S.userid = UA.userid
    AND S.schedulefrom >= UA.availablefrom
    AND S.scheduletill <= UA.availabletill
WHERE
    S.userid <> 1

This will show you all users whose availability is within their schedule periods.

Upvotes: 1

Related Questions