MySql version 8.0.17 exclude rows where NOT LIKE and OR condition

This is a part of list records in the table stored a database MySql version 8.0.17

+---------------+---------------------+-----------+---------------------+---------------------+------------+
| tkt           | tdate               | tstate    | tigx                | tclosed             | tcompleted |
+---------------+---------------------+-----------+---------------------+---------------------+------------+
| 098720D045584 | 2020-12-21 21:22:00 | Forwarded | 2021-01-05 10:24:00 | NULL                | NULL       |
| 098720B045582 | 2020-12-21 20:15:00 | Forwarded | 2021-01-05 10:27:00 | NULL                | NULL       |
| 098720B045560 | 2020-12-21 17:02:00 | Forwarded | 2021-01-05 10:19:00 | NULL                | NULL       |
| 098720B045549 | 2020-12-21 16:36:00 | Forwarded | 2021-01-05 10:08:00 | NULL                | NULL       |
| 098720B045537 | 2020-12-21 14:57:00 | Forwarded | 2021-01-05 10:33:00 | NULL                | NULL       |
| 098720D045536 | 2020-12-21 14:57:00 | Closed    | NULL                | 2020-12-21 17:39:00 | NULL       |
| 098720L045296 | 2020-12-21 09:00:00 | Closed    | NULL                | NULL                | NULL       |
| 098720B045470 | 2020-12-21 08:55:00 | Forwarded | 2021-01-05 10:21:00 | NULL                | NULL       |
+---------------+---------------------+-----------+---------------------+---------------------+------------+
8 rows in set (0.14 sec)

I have to exclude this row from return

+---------------+---------------------+-----------+---------------------+---------------------+------------+
| tkt           | tdate               | tstate    | tigx                | tclosed             | tcompleted |
+---------------+---------------------+-----------+---------------------+---------------------+------------+
| 098720L045296 | 2020-12-21 09:00:00 | Closed    | NULL                | NULL                | NULL       |
+---------------+---------------------+-----------+---------------------+---------------------+------------+

Because

  1. the number of tkt contains L letter
  2. the tstate is closed
  3. the tigx is NULL
  4. the tclosed is NULL
  5. the tcompleted is NULL

I have tried without success this query because the row is present in return sql

SELECT
    tkt,
    tdate,
    tstate,
    tigx,
    tclosed,
    tcompleted
FROM
    `tbl_c` 
WHERE
    ((
        `tkt` NOT LIKE '%I%' 
    OR  `tkt` NOT LIKE '%L%' AND tstate IN ( 'Closed' ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL )) 
    AND LEFT ( `tigx`, 1 ) NOT IN ( 'M' ) 
ORDER BY
    tdate DESC;

Answer

Translating your requirements literally:

SELECT *
FROM tbl_c
WHERE NOT (tkt LIKE '%L%' AND tstate = 'Closed' AND tigx IS NULL AND
           tclosed IS NULL AND tcompleted IS NULL);

Leave a Reply

Your email address will not be published. Required fields are marked *