Inner join on output of a subquery

Question

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

• Akina: 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.
• j10: @Akina : in that case will subquery be executed twice ?
• Akina: 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.

Answer 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.

• j10: 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: what indexes would be helpful to speed up the query.
• Kondybas: @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)
• Rick James: A column alias cannot be used in the first parts of a SELECT, only in (I think) the GROUP BY and later.