0

We have Slot table which tells us about freetime for an employee between starttime and endtime.

We need to find Slot which have freetime > 'x' mins. This could be either be a complete slot or it can be consecutive slot for same employee(in this case we show the first slot to user).

We are using MySQL 5.7

  `CREATE TABLE `Slot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `freetime` time NOT NULL,
  `assigned_To_Id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1

We tried following query :

Select        distinct S1.starttime, 
               S1.endtime 
from 
    (Select * from `Slot`
     where     `assigned_To_Id` in (2, 3, 4) 
     and       `freetime`  > 0 
     and       `starttime` >= '2018-08-08 00:00'
     and       `endtime`   <= '2018-08-08 23:59'
     order by `Slot`.`starttime` ASC) as S1

inner join S1 as S2
 on        (S1.freetime >'00:10:00') 
 or        (S2.starttime = S1.starttime + INTERVAL 60 MINUTE 
 and       ADDTIME(S1.freetime, S2.freetime) > '00:10:00'
 and       S1.assigned_To = S2.assigned_To)

We are getting stuck as to how to run self join on output of S1- its gives error saying that S1 table does not exist.

We read online and found about Common Table Expressions but we are using 5.7 version.

We are not sure how to go about doing inner join on same query.

Example :

|id| startime            | endtime             | assigned_To_Id | freetime
|1 | 2018-07-07 11:00:00 | 2018-07-07 12:00:00 |   2            | 00:00:05
|2 | 2018-07-07 12:01:00 | 2018-07-07 13:00:00 |   2            | 00:00:15
|3 | 2018-07-07 13:01:00 | 2018-07-07 14:00:00 |   2            | 00:00:05
|4 | 2018-07-07 15:01:00 | 2018-07-07 16:00:00 |   2            | 00:00:15

Output Should be :

|1 | 2018-07-07 11:00:00 | 2018-07-07 12:00:00 |   2            | 00:00:05
|2 | 2018-07-07 12:01:00 | 2018-07-07 13:00:00 |   2            | 00:00:15
|4 | 2018-07-07 15:01:00 | 2018-07-07 16:00:00 |   2            | 00:00:15
3
  • 1
    Replace S1 reference in INNER JOIN with the second copy of subquery text. And remove ORDER BY from subquery - without LIMIT it will not affect on the result.
    – Akina
    Aug 2, 2018 at 12:48
  • @Akina : in that case will subquery be executed twice ?
    – j10
    Aug 3, 2018 at 5:13
  • It seems subqueries are not cached in MySQL - if so the answer is No... you may try to convert it from subquery to a view - it seems to be cached.
    – Akina
    Aug 3, 2018 at 5:19

1 Answer 1

1

You can do all that without subqueries:

SELECT DISTINCT S1.starttime
     , S1.endtime 

  FROM Slot AS S1
  JOIN Slot AS S2  ON S2.assigned_To_Id = S1.assigned_To_Id
                  AND S1.assigned_To_Id IN (2, 3, 4) 
                  AND S1.id != S2.id -- a little trick to prevent joining rows with itself 
                                     -- slightly reduce the derivative table size 
                                     -- and speed up the overall performance

 WHERE S1.starttime >= '2018-08-08 00:00'
   AND S1.endtime <= '2018-08-08 23:59'
   AND S1.freetime > 0 
   AND ( S1.freetime > '00:10:00' 
         OR ( S2.starttime = S1.starttime + INTERVAL 60 MINUTE 
              AND ADDTIME(S1.freetime, S2.freetime) > '00:10:00' ))

 ORDER BY S1.starttime ASC
;

In fact aliasing is not transitive operation in the mysql. Say, simple query

SELECT 1 AS a, a AS b;

yeild an error Unknown column 'a' in 'field list'. So you can create an alias for the real object not for another alias.

4
  • Thank you Kondybas. But you missed a condition that either S1.freetime > '00:10:00' or (S2.starttime = S1.starttime + INTERVAL 60 MINUTE AND ADDTIME(S1.freetime, S2.freetime) > '00:10:00') -- so either S1 completely or S1 and S2
    – j10
    Aug 3, 2018 at 5:12
  • what indexes would be helpful to speed up the query.
    – j10
    Aug 3, 2018 at 5:55
  • @j10 you need a multicolumn index containing all columns of Slot table mentioned in the JOIN / WHERE / ORDER BY clauses. In your case the good candidate is the (starttime, endtime, freetime, assigned_To_Id)
    – Kondybas
    Aug 3, 2018 at 7:44
  • A column alias cannot be used in the first parts of a SELECT, only in (I think) the GROUP BY and later.
    – Rick James
    Aug 21, 2018 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.