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

164
January 09, 2018, at 4:30 PM

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
                        );
Answer 2
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'
ORDER BY timestamp_val desc 
LIMIT 1
Rent Charter Buses Company
READ ALSO
how to check the php data base regularly for change or update in it?

how to check the php data base regularly for change or update in it?

i am creating an application,which uses the php mysql database, which contains a employee detailslike schedule date,hours etc

187
Sql Query, not retrieving correct values with CURDATE() function

Sql Query, not retrieving correct values with CURDATE() function

I need to retrieve records from database where my 'dateOfTermination' value is equal to today's dateI used the below query, but it won't show any results

211