Inner join on output of a subquery 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`  &gt; 0 
     and       `starttime` &gt;= '2018-08-08 00:00'
     and       `endtime`   &lt;= '2018-08-08 23:59'
     order by `Slot`.`starttime` ASC) as S1

inner join S1 as S2
 on        (S1.freetime &gt;'00:10:00') 
 or        (S2.starttime = S1.starttime + INTERVAL 60 MINUTE 
 and       ADDTIME(S1.freetime, S2.freetime) &gt; '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
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 &gt;= '2018-08-08 00:00'
   AND S1.endtime &lt;= '2018-08-08 23:59'
   AND S1.freetime &gt; 0 
   AND ( S1.freetime &gt; '00:10:00' 
         OR ( S2.starttime = S1.starttime + INTERVAL 60 MINUTE 
              AND ADDTIME(S1.freetime, S2.freetime) &gt; '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.