CODEWITHSUNDEEP
mysqlstatements
user3567212
user3567212

Reputation: 101

MySQL - Compare query results to current table data

I have the following two tables:

Table1

|  id |  name |  job    |      start_time      |     end_time          |
| ----| ------| --------| ---------------------| ----------------------|
| 111 |  John |  Janitor|  2016-08-20 00:01:00 |       NULL            |
| 111 |  John |  Janitor|        NULL          |   2016-08-20 00:02:00 |
| 222 |  Sam  |  Valet  |  2016-08-20 00:03:00 |          NULL         |
| 222 |  Sam  |  Valet  |        NULL          |   2016-08-20 00:04:00 |

Table2

|  name |  job    |      checkin_time    |
| ------| --------| ---------------------|
|  John |  Janitor|  2016-08-20 00:01:30 |
|  Sam  |  Valet  |  2016-08-20 00:03:30 |
|  Tim  |  Cashier|  2016-09-20 00:01:00 |

The following query 

SELECT id, Table2.name, Table2.job, start_time, Table2.checkin_time, end_time FROM (
SELECT id,name,job, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM Table1
GROUP BY id
) AS results INNER JOIN Table2 ON 
  results.job = Table2.job
  AND results.name = Table2.name
  AND (Table2.checkin_time BETWEEN results.start_time AND results.end_time OR
       Table2.checkin_time >= results.start_time AND results.end_time IS NULL);

will display:

|  id |  name |  job    |      start_time      |     checkin_time    |  end_time    |       
| ----| ------| --------| ---------------------| --------------------|----------------|
| 111 |  John |  Janitor|  2016-08-20 00:01:00 | 2016-08-20 00:01:30 |2016-08-20 00:02:00 |
| 222 |  Sam  |  Valet  |  2016-08-20 00:03:00 | 2016-08-20 00:03:30 |2016-08-20 00:04:00 |

How do I formulate my query so that it will return the record that was not successful/found. E.g. From Table1, the record for:

|  Tim  |  Cashier|  2016-09-20 00:01:00 |

THANK YOU in advance for your help community team!

Upvotes: 0

Views: 43

Answers (2)

Quote
Quote

Reputation: 41

You can use a NOT-EXISTS clause to perform the task: here you find rows in table2 that are not in your selection result.

SELECT
  name, job, checkin_time
FROM
  table2
WHERE NOT EXIST (
  SELECT *
  FROM
    v
  WHERE
    v.name = table2.name)

Combining together:

Create View v AS
(SELECT id, Table2.name, Table2.job, start_time, Table2.checkin_time, end_time FROM (
SELECT id,name,job, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM Table1
GROUP BY id
) AS results INNER JOIN Table2 ON 
  results.job = Table2.job
  AND results.name = Table2.name
  AND (Table2.checkin_time BETWEEN results.start_time AND results.end_time OR
   Table2.checkin_time >= results.start_time AND results.end_time IS NULL));

SELECT
  name, job, checkin_time
FROM
  table2
WHERE NOT EXIST (
  SELECT *
  FROM
    v
  WHERE
    v.name = table2.name)

Upvotes: 0

Imbar M.
Imbar M.

Reputation: 1114

you can do a right join and filter only the "no matches" using nulls:

SELECT table2.*
FROM (
    SELECT id,name,job, MIN(start_time) AS start_time, MAX(end_time) AS end_time
    FROM Table1
    GROUP BY id) AS results
RIGHT JOIN Table2 ON 
    results.job = Table2.job
    AND results.name = Table2.name
    AND (Table2.checkin_time BETWEEN results.start_time AND results.end_time OR
        Table2.checkin_time >= results.start_time AND results.end_time IS NULL)
WHERE results.id IS NULL

Upvotes: 1

Related Questions