MYSQL: Fetching latest rows from second table based upon timestamp value

Question

I have a table table1 a_id as PK, ipaddress, create_dt Here ipaddress is varchar, create_dt is datetime

a_id    ip              create_dt
9205    10.10.10.10     2017-01-07 08:03:32
9206    10.10.10.11     2017-01-06 08:03:32
9207    10.10.10.12     2015-01-07 08:03:32

---more than 1000 rows

I have another mysql table with following columns id as PK, ip, check_type check_status, a_id and created_dt: Here a_id is foreign key from table1 and created_dt is datetime

id      ip             check_type    check_status  a_id    timestamp_val
1       10.10.10.10    check1        FAIL          9205    2017-01-07 10:03:32
2       10.10.10.10    check2        PASS          9205    2017-01-07 10:03:32
3       10.10.10.10    check1        FAIL          9205    2016-11-07 10:03:32
4       10.10.10.10    check2        PASS          9205    2016-11-07 10:03:32
5       10.10.10.11    check1        PASS          9206    2017-01-06 10:03:32
6       10.10.10.11    check2        PASS          9206    2015-01-06 10:03:32

I want all rows from table1 where date(create_dt) >= '2017-01-07' and table1.a_id = table2.a_id and table2.check1 = 'FAIL' Also, I only want to consider the row from table2 with latest timestamp_val

So from the above example, my query should return

a_id    ip              create_dt
9205    10.10.10.10     2017-01-07 08:03:32

I have written the following query and want to know how to incorporate the logic to consider the row from table2 with latest 'timestamp_val'

SELECT
  *
FROM table1 a
INNER JOIN table2 b
  ON a.a_id = b.a_id
  AND DATE(a.create_dt) >= '2017-01-07'
  AND b.check_status = 'FAIL'
  AND b.check_type = 'check1'

Answer 1

The date() function doesn't do anything for you. Also, the individual comparisons would normally be in a where clause.

You can then do what you want with an additional condition in the where clause:

SELECT *  
FROM table1 a INNER JOIN
     table2 b
     ON a.a_id = b.a_id
WHERE a.create_dt >= '2017-01-07' AND
      b.check_status = 'FAIL' AND
      b.check_type = 'check1' AND
      b.timestamp_val = (SELECT MAX(b2.timestamp_val)
                         FROM table2 b2
                         WHERE b2.a_id = b.a_id AND
                               b2.check_status = b.check_status AND
                               b2.check_type = b.check_type
                        );