steve steve - 2 months ago 6
MySQL Question

SELECT items from DB comparing to latest date in another table

I would like to select all items from

userLogTable
where the record is newer than the latest matching item in
activityTable
. I would like to do this in a single query.

Currently I select from one table and then loop through the results while matching against another table.

SELECT COUNT(*) AS Visits, userLogTable.* FROM userLogTable GROUP BY Name, Date

userLogTable
Name | Surname | Date
-----------------------------
Dave | Smith | 2016-06-01
Jane | Doe | 2016-06-01
Dave | Smith | 2016-06-02
Dave | Smith | 2016-06-01
Jane | Doe | 2016-06-03
Peter | Bloggs | 2016-06-03
Steve | Foo | 2016-06-01
Steve | Foo | 2016-06-01
// many more rows

// above SQL returns the following result as expected/needed
Name | Surname | Date | Visits
----------------------------------------
Dave | Smith | 2016-06-01 | 2
Jane | Doe | 2016-06-01 | 1
Dave | Smith | 2016-06-02 | 1
Jane | Doe | 2016-06-03 | 1
Peter | Bloggs | 2016-06-03 | 1
Steve | Foo | 2016-06-01 | 2

activityTable
Name | Surname | Date
------------------------------
Dave | Smith | 2016-06-03
Dave | Smith | 2016-06-03
Dave | Smith | 2016-06-03
Dave | Smith | 2016-06-02
Dave | Smith | 2016-06-02
Dave | Smith | 2016-06-02
Dave | Smith | 2016-06-01
Dave | Smith | 2016-05-29
Dave | Smith | 2016-05-29
// many more rows


QUERY:

foreach($userLogTableResult as $key => $val) {
// db function
SELECT Date
FROM activityTable
WHERE Date > $latestDateFromUserLogTable
AND NAME = $val['Name']
AND Surname = $val['Surname']
ORDER BY Date DESC LIMIT 1
// if there is a result then unset this item as it's older than the latest activity
}


Any help would be greatly appreciated.

The code above has been extensively simplified for this example. I am building the SQL and parsing it into a custom PDO function. All values are escaped and sanitised accordingly.

Answer

I think this does what you want:

select ult.*
from userLogTable ult
where ult.date > (select max(a.date)
                  from activityTable a
                  where a.name = ult.name and a.surname = ult.surname
                 ) ;
Comments