How to use the MySQL NOT EXISTS operator

Guido1971 · May 17, 2020, 4:20pm

Hi gurus,

I have problem with MySQL database

On the table calendar_recovery_interval_hour_2020 I divided the days into time slots of one hour each, e.g. 2020-04-14

+---------------------+---------------------+-----+
| start_date          | end_date            | sID |
+---------------------+---------------------+-----+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |   1 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |   2 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |   3 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |   4 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |   5 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |   6 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |   7 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |   8 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |   9 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |  10 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |  11 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |  12 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |  13 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |  14 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |  15 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |  16 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |  17 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |  18 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |  19 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |  20 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |  21 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |  22 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |  23 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |  24 |
+---------------------+---------------------+-----+
24 rows in set

Now I need to find in a second table stable_2020 for the time slots missing with respect to table calendar_recovery_interval_hour_2020

+------+---------------------+-----+
| STUX | sdatetime           | sID |
+------+---------------------+-----+
|   14 | 2020-04-14 01:09:00 |   1 |
|   14 | 2020-04-14 01:59:00 |   2 |
|   14 | 2020-04-14 02:02:00 |   3 |
|   14 | 2020-04-14 02:52:00 |   4 |
+------+---------------------+-----+
4 rows in set

This the tutorial

On this example I need the output below, because on stable_2020 I have four rows

Two rows on time slot

| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |   2 |

Other two rows on time slot

| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |   3 |

output required

+---------------------+---------------------+
| start_date          | end_date            |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+

The code I’ve tried below without success

mysql> SELECT DISTINCT
    t.start_date,
    t.end_date
FROM
    `calendar_recovery_interval_hour_2020` t,
    `stable_2020` m
WHERE
    m.`sdatetime` NOT BETWEEN Cast(t.start_date AS DateTime)
AND Cast(t.end_date AS DateTime);
+---------------------+---------------------+
| start_date          | end_date            |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+
24 rows in set

And

mysql> SELECT
    start_date,
    end_date
FROM
    `calendar_recovery_interval_hour_2020` t
WHERE
    EXISTS (
        SELECT
            1
        FROM
            `stable_2020` m
        WHERE
            m.`sdatetime` NOT BETWEEN t.start_date
        AND t.end_date
        ORDER BY
            m.`sdatetime` DESC
    );
+---------------------+---------------------+
| start_date          | end_date            |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+
24 rows in set

My structure table below

DROP TABLE IF EXISTS `stable_2020`;
CREATE TABLE `stable_2020` (
  `STUX` int(11) DEFAULT NULL,
  `sdatetime` datetime DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`),
  UNIQUE KEY `ukey` (`STUX`,`sdatetime`) USING BTREE,
  KEY `sdatetime` (`sdatetime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of stable_2020
-- ----------------------------
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 01:09:00', '1');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 01:59:00', '2');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 02:02:00', '3');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 02:52:00', '4');


DROP TABLE IF EXISTS `calendar_recovery_interval_hour_2020`;
CREATE TABLE `calendar_recovery_interval_hour_2020` (
  `start_date` datetime DEFAULT NUL
  `end_date` datetime DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`)
) ENGINE=InnoDB CHARSET=latin1;

-- ----------------------------
-- Records of calendar_recovery_interval_hour_2020
-- ----------------------------
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 00:00:00', '2020-04-14 00:59:00', '1');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 01:00:00', '2020-04-14 01:59:00', '2');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 02:00:00', '2020-04-14 02:59:00', '3');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 03:00:00', '2020-04-14 03:59:00', '4');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 04:00:00', '2020-04-14 04:59:00', '5');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 05:00:00', '2020-04-14 05:59:00', '6');
INSERT

m_hutley · May 17, 2020, 11:02pm

I think what you’re describing at this point is

SELECT start_date, end_date
FROM `calendar_recovery_interval_hour_2020`
WHERE start_date NOT IN(
  SELECT sdatetime 
  FROM `stable_2020`
)

m_hutley · May 18, 2020, 12:37pm

Oh sorry, I didnt look closely enough at your stable_2020 data. It’s not necessarily matching. Gotcha.

SELECT start_date,end_date 
FROM `calendar_recovery_interval_hour_2020` i
LEFT JOIN `stable_2020` s 
ON i.start_date = CAST(DATE_FORMAT(s.sdatetime,'%Y-%m-%d %h:00:00') AS DATETIME)
WHERE STUX IS NULL;

(The cast may actually be unnecessary, i haven’t tested.)

Guido1971 · May 18, 2020, 3:08pm

Many thanks for this help.

system · August 17, 2020, 10:08pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.