Help with MySQL Query

Discussion in 'MySQL' started by montyonthebonty, May 3, 2017.

0
  1. #1
    I am developing a page that needs to log calls from workers signing on and off at remote locations. I have two tables:

    tblLoneWorking - lists all the calls that we should have, with the fields Location, Time and Type

    tblLoneWorkingCalls - lists all the calls we have received, with the fields timeStamp, Location and Type.

    I want to retrieve all the records from tblLoneWorking where there isn't a corresponding record of the same location and type for today AND the time is earlier than now (i.e. only ones that are overdue).

    So far I have got this but it's not working. Any ideas?

    Thanks
    Chris 

    SELECT tblLoneWorking.Location, tblLoneWorking.Time, tblLoneWorking.Type
FROM tblLoneWorking
WHERE NOT EXISTS
(SELECT tblLoneWorking.Location
FROM tblLoneWorking
INNER JOIN tblLoneWorkingCalls ON tblLoneWorkingCalls.Location=tblLoneWorking.Location
WHERE  DATE(tblLoneWorkingCalls.timeStamp) = DATE(NOW())
AND tblLoneworkingCalls.type=tblLoneWorking.type)
    Code (SQL):
     
    montyonthebonty, May 3, 2017 IP