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.

  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
    SEMrush
    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
    SEMrush