1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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