MySQL: Finding servers from another table

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: Here a_id is foreign key from table1

id      ip             check_type    check_status  a_id
1       10.10.10.10    check1        FAIL          9205
2       10.10.10.10    check2        PASS          9205
3       10.10.10.11    check1        PASS          9206
4       10.10.10.11    check2        PASS          9206

I want all rows from table1 where date(create_dt) >= '2017-01-07' and table1.a_id = table2.a_id and table2.check1 = 'FAIL'

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 if there is some better way to write the query. The below query seems to be little slower

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

Answer 1

Do the fact you are using an inner join you could assign the condition in where directly in th on clause

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'

The result is the same .. is just a different way for the jon condition expression