I have a table table1 a_id as PK, hostname, create_dt
Here ipaddress is varchar, create_dt is datetime
a_id hostname create_dt
9205 abc.com 2017-01-07 08:03:32
9206 cde 2017-01-06 08:03:32
9207 abc2.com 2015-01-07 08:03:32
---more than 1000 rows
I have another mysql table with following columns id as PK, name, and created_dt:
and created_dt is datetime
id name created_dt
1 abc 2017-01-07 10:03:32
2 cde.com 2017-01-07 10:03:32
3 abc2.com 2016-11-07 10:03:32
4 abc3.com 2016-11-07 10:03:32
5 abc4.com 2017-01-06 10:03:32
6 abc5.com 2015-01-06 10:03:32
I want to compare table1 and table2 using hostname and name and bring all rows from table2 which have hostname existing in table1
So from the above example, my query should return
id name timestamp_val
1 abc 2017-01-07 10:03:32
2 cde.com 2017-01-07 10:03:32
3 abc2.com 2016-11-07 10:03:32
I have written the following query
SELECT *
FROM table2.name a
INNER JOIN table1.hostname b
where a.name LIKE CONCAT(b.hostname, '%');
You can use SUBSTRING_INDEX , I did not try this query but below query will look a like what you want.
Try this :
SELECT t2.*
FROM table2 t1, table2 t2
WHERE (SUBSTRING_INDEX(t1.hostname, '.', 1) = SUBSTRING_INDEX(t2.name, '.', 1))